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?
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?