Macro Error

[acss]

I create a pivot table monthly for two data sheets in a workbook. I want to
have the steps of creating the pivots automated so i am using a macro but i
receive a run-time error "9" Subscript out of Range. Could it be that in the
macro creating process , i included the naming of the pivot sheets?

 

[Bob Flanagan]

The macro recorder is very literal. If you have changed sheet names, did a
number of records before you were happy with the results, the table names
themselves will be different. The challenge with recorded macros is to
modify them so that they are robust enough to handle your situation. If you
post your code (assuming it is not a lot of lines), lots of people will
provide suggestions.

Robert Flanagan
Macro Systems
Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

 

[acss]

The code for the macro is:
Sub Macro1()
Range("A1:C4").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R4C3").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("description")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("amount"), "Sum of amount", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("code")
.Orientation = xlColumnField
.Position = 1
End With
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "flowers"
Range("C24").Select
End Sub

 

[Bob Flanagan]

Does sheet1 exist - it is being referred to in the macro to create the
table. Does sheet4 exists?

BTW, are the ranges still correct? Typically data changes and recorded
macros for pivot table require range modifications.

Does the worksheet already have a pivot table on it? If so, the code is
likely to give strange results and perhaps not work.

Let us know which line is causing an error.

Robert Flanagan
Macro Systems
144 Dewberry Drive
Hockessin, Delaware, U.S. 19707

Phone: 302-234-9857, fax 302-234-9859
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel