Using a macro to create multiple PivotTables

[enbentro]

Hello,

I have open/high/low/close price data for thirty stocks, and each stock has
its own worksheet. I wanted to write a macro that would create a pivottable
on sheet 1 using the price data on sheet 1, then automatically move to
worksheet 2, create a pivottable on sheet 2 using the price data on sheet 2,
and so on until all thirty worksheets had their own pivottable. With help
from this site (thanks Tom Ogilvy), I recorded a macro which worked perfectly.

For some reason, I made a minor change to the section of code which creates
the pivottable, and it no longer works. I have tried unsuccessfully to fix
the problem, but to no avail. The macro code appears below:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/16/2004 by Renee/Eric
'
' Keyboard Shortcut: Ctrl+h
'
Dim ws As Worksheet
For Each ws In Worksheets (Array("Sheet29", "Sheet28", "Sheet27", _
"Sheet26", "Sheet25", "Sheet24", "Sheet23", "Sheet22", "Sheet21",
"Sheet20", _
"Sheet19", "Sheet18", "Sheet17", "Sheet16", "Sheet15", "Sheet14",
"Sheet13", _
"Sheet12", "Sheet11", "Sheet10", "Sheet9", "Sheet8", "Sheet7", "Sheet6",
"Sheet5", _
"Sheet4", "Sheet3", "Sheet2", "Sheet1"))
ws.Activate

Rows("1:4").Select
Selection.Delete Shift:=xlUp
Range("C,F:F").Select
Range("F1").Activate
Selection.Delete Shift:=xlToLeft
Range("D1").Select
ActiveCell.FormulaR1C1 = "% Chg"
Columns("D").Select
Selection.NumberFormat = "0.00%"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[-2])/RC[-2]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2170"), Type:=xlFillDefault
Range("D2170").Select
Range("A1").CurrentRegion.Select

The error is somewhere in these 5 lines of code:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Name & "!r1c1:r170c4").CreatePivotTable
TableDestination:= _
"'[DJIA Components.xls]" & ActiveSheet.Name & "'!R2C6",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

With ActiveSheet.PivotTables("PivotTable1").PivotFields("DATE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("% Chg"), "Sum of % Chg", xlSum
Range("F2").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of % Chg")
.Function = xlAverage
.NumberFormat = "0.00%"
End With
Range("F4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False,
False, _
False, True, False, False)
Next
End Sub


Can anyone see what I may have altered so as to make macro unusable? I wish
I could remember!

Thanks in advance for any insight-
Eric Bentrovato

 

[Jan Karel Pieterse]

Hi Enbentro,

Can anyone see what I may have altered so as to make macro unusable? I wish
I could remember!

All I can see quickly, is that your code will fail with sheetnames that contain
spaces in them. This line of code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Name & "!r1c1:r170c4").CreatePivotTable

needs to be changed to:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & ActiveSheet.Name & "'!r1c1:r170c4").CreatePivotTable

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Tom Ogilvy]

If Jan Karel's suggestion doesn't change anything then:

I don't see anything obvious. Try rerecording the macro as you create the
pivotTable manually. Then compare that to what you have and see if you see
a significant difference.

--
Regards,
Tom Ogilvy

Hello,

I have open/high/low/close price data for thirty stocks, and each stock has
its own worksheet. I wanted to write a macro that would create a pivottable
on sheet 1 using the price data on sheet 1, then automatically move to
worksheet 2, create a pivottable on sheet 2 using the price data on sheet 2,
and so on until all thirty worksheets had their own pivottable. With help
from this site (thanks Tom Ogilvy), I recorded a macro which worked perfectly.

For some reason, I made a minor change to the section of code which creates
the pivottable, and it no longer works. I have tried unsuccessfully to fix
the problem, but to no avail. The macro code appears below:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/16/2004 by Renee/Eric
'
' Keyboard Shortcut: Ctrl+h
'
Dim ws As Worksheet
For Each ws In Worksheets (Array("Sheet29", "Sheet28", "Sheet27", _
"Sheet26", "Sheet25", "Sheet24", "Sheet23", "Sheet22", "Sheet21",
"Sheet20", _
"Sheet19", "Sheet18", "Sheet17", "Sheet16", "Sheet15", "Sheet14",
"Sheet13", _
"Sheet12", "Sheet11", "Sheet10", "Sheet9", "Sheet8", "Sheet7", "Sheet6",
"Sheet5", _
"Sheet4", "Sheet3", "Sheet2", "Sheet1"))
ws.Activate

Rows("1:4").Select
Selection.Delete Shift:=xlUp
Range("C,F:F").Select
Range("F1").Activate
Selection.Delete Shift:=xlToLeft
Range("D1").Select
ActiveCell.FormulaR1C1 = "% Chg"
Columns("D").Select
Selection.NumberFormat = "0.00%"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[-2])/RC[-2]"
Range("D2").Select
Selection.AutoFill Destination:=Range("D2170"), Type:=xlFillDefault
Range("D2170").Select
Range("A1").CurrentRegion.Select

The error is somewhere in these 5 lines of code:
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Name & "!r1c1:r170c4").CreatePivotTable
TableDestination:= _
"'[DJIA Components.xls]" & ActiveSheet.Name & "'!R2C6",
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10

With ActiveSheet.PivotTables("PivotTable1").PivotFields("DATE")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("% Chg"), "Sum of % Chg", xlSum
Range("F2").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of % Chg")
.Function = xlAverage
.NumberFormat = "0.00%"
End With
Range("F4").Select
Selection.Group Start:=True, End:=True, Periods:=Array(False, False,
False, _
False, True, False, False)
Next
End Sub


Can anyone see what I may have altered so as to make macro unusable? I wish
I could remember!

Thanks in advance for any insight-
Eric Bentrovato