PDA

View Full Version : Solved: Looping Creation of Pivot Table



compariniaa
06-27-2006, 10:59 AM
I have a macro that, depending on dates specified by the user, creates one worksheet per quarter. The sheet names are stored in cells J2 and down. the next step in the macro would be to create Pivot Tables (all the same, except for the quarter they refer to) in each of the worksheets. My problem is that I can't get the Pivot Table destination right. I tried setting J2's (or the next cell) value as a variable then inserting the variable into the destination part of my macro, but it doesn't work, I get "Invalid Procedure Call or Argument" This is part of my sub:
Sub Create_Pivot()
Dim RowNum As Integer
Dim Ws As String

RowNum = 2
Ws = Sheets("Cell References").Range("J" & RowNum).Value
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Combined!C1:C16").CreatePivotTable TableDestination:= _
Ws & "R1C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion10
And then the rest of the code just specifies what fields and other Pivot options. I'd also like to use a "Do While Sheets("Cell References").Range("J"&RowNum).valu<>0" loop for this. What am I doing wrong?

acw
06-27-2006, 10:52 PM
Hi

I'm presuming that the string ws is the name of the sheet you want the pivot table to go to so try modifying the tabledestination part of the code to

tabledestination:=sheets(ws).range("a1")

HTH

Tony

compariniaa
06-28-2006, 07:45 AM
Thank you, it worked
Yes, the string ws is the name of the destination sheet. good call, sorry for not specifying. and the "RowNum" is an integer allowing me to change ws in my loop (if I can ever get past this new problem). now I'm gettign a problem with the last line (beginning with "defaultversion..."). It's an "application-defined or object defined error". Any idea what's going on?

Ws = Sheets("Cell References").Range("J" & RowNum).Value
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("Combined").Range("A:P65536")).CreatePivotTable TableDestination:= _
Sheets(Ws).Range("A1"), TableName:="PivotTable10" _
, DefaultVersion:=xlPivotTableVersion10

compariniaa
06-28-2006, 09:25 AM
I had recorded a macro where I made the pivot table and did a bunch of other stuff. I started over and just did the pivot and after a few modifications it runs fine. Thanks for trying :)