Expert: Tom Ogilvy - 9/25/2008

Question
QUESTION: Hi Tom,

I fixed last problem. The one about the rows. It was an error from the list I was copying such information. Thanks anyway.

Right now I'm developing a code that updates the values in a pivot table. Every time I update a worksheet ("Data May '08 - ..."), I add rows of information below the last written row. The pivot table I have for that must be updated, but the range increments in rows everytime there's an update.

Is there a way to look for the last written row in sheet ("Data May '08 - ...") and put that number in the pivot table source data?

My code looks like this:

   ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
       "'Data May ''08 - ...'!R1C1:R2330C32").CreatePivotTable TableDestination:="" _
       , TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
   ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
   ActiveSheet.Cells(3, 1).Select

This value, R1C1:R2330C32, is the one that must be updated each time I run the macro. For example, if last written row is 2780, then this code must be written like: R1C1:R2780C32. Columns don't vary, so I'm only interested in the second R.

How can I do this?

Thanks for your help.

ANSWER: Rafael,

Dim rw as Long
with worksheets("Data May ''08 - ...'")
 set rw = .cells(rows.count,1).end(xlup).row
end with

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
  SourceData:="'Data May ''08 - ...'!R1C1:R" _
     rw & "C32").CreatePivotTable _
     TableDestination:="", _
     TableName:="PivotTable1", _
     DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard _
  TableDestination:=ActiveSheet.Cells(3, 1)
  ActiveSheet.Cells(3, 1).Select

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Tom,

There's must be an error somewhere cause when I copied the code into the VB editor it displayed this part of the code in red:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
 SourceData:="'Data May ''08 - ...'!R1C1:R" _
    rw & "C32").CreatePivotTable _
    TableDestination:="", _
    TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion10

Then, I changed my code to this:

   Sheets("Data May '08 - ...").Select
   Dim rw As Long
       With Worksheets("Data May ''08 - ...'")
       Set rw = .Cells(Rows.Count, 1).End(xlUp).Row
   End With
   ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
       "'Data May ''08 - ...'!R1C1:R _ rw&C32").CreatePivotTable TableDestination:="" _
       , TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
   ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
   ActiveSheet.Cells(3, 1).Select
   ActiveWorkbook.ShowPivotTableFieldList = True

And when I run the macro a message box display a Compile error that says Object required, focusing in the line Set rw =.

What should I do?


Answer

Rafael,
There were a couple of typos on my part.  This compiled for me:


Dim rw As Long
With Worksheets("Data May ''08 - ...")
rw = .Cells(Rows.Count, 1).End(xlUp).Row
End With

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
 SourceData:="'Data May ''08 - ...'!R1C1:R" & _
    rw & "C32").CreatePivotTable _
    TableDestination:="", _
    TableName:="PivotTable1", _
    DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard _
 TableDestination:=ActiveSheet.Cells(3, 1)
 ActiveSheet.Cells(3, 1).Select

If it says subscript out of range, check the name of the worksheet in Worksheets("Data May ''08 - ...")
since that would be the source of the problem - but as I said it compiled for me and the name I had on the tab is

Data May '08 - ...

--
Regards,
Tom Ogilvy
  


--
Regards,
Tom Ogilvy