Pivot Table Woes (Excel 2003 – 3)

Topic

New Reply

I am having a problem creating a pivot table using a variable to get the name of the file. Here is the code:

Set wb1 = activeworkbook

Now is the confusing part. The first set of lines works fine, but it uses the actual name of the file:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
“Tickets”).CreatePivotTable TableDestination:= _
“‘[Copy of July Service Report Data_J.xls]Pivot Tables’!R3C1”, TableName:= _
“IBD Table”, DefaultVersion:=xlPivotTableVersion10

However, this code fails each time and I don’t know why. The only difference is that I am using a variable in place of the actual filename. Any ideas?

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
“Tickets”).CreatePivotTable TableDestination:= _
“‘[” & wb1.Name & “]Pivot Tables’!R3C1″, TableName:=”IBD Table”, DefaultVersion:= _
xlPivotTableVersion10

As always, my grateful thanks for all the help…

Reply | Quote

Replies

Try

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
“Tickets”).CreatePivotTable TableDestination:= _
Worksheets(“Pivot Tables”).Range(“A3″), TableName:=”IBD Table”, DefaultVersion:= _
xlPivotTableVersion10

Reply | Quote

Hi Hans,

Tried your version, but it did not work either. I get Run-time error 438, “Object doesn’t support this property or method.”

Very vexing…

Reply | Quote

I finally got it! Here is what finally worked for me:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
“Tickets”).CreatePivotTable TableDestination:= _
“‘[” & wb1.name & “]PivotTables’!R3C1″,TableName:=”IBD Table”, DefaultVersion:= _
xlPivotTableVersion10

Thank you for your efforts and for making me focus…

Reply | Quote