excel macro to create pivottable

[wally32]

im looking to make my job that bit quicker by introducing a macro to help analysis spreadsheets within a workbook.
within the workbook there will be 12 worksheets (one for each month), where i be analysing some data through a pivottable. creating the pivottable is fine. the information i be looking to analyse will be in column c - h and number of rows maybe different from month to month.
i recorded a macro from start of highlighting cells to creating the final pivottable. when i try the same macro for a different month is comes up with few errors. the macro code i recorded is below
ub Macro2()
'
' Macro2 Macro
' Macro recorded 17/03/2011 by waltek
'
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Feb 2011'!C3:C7").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Score"), "Count of Score", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Score")
.Orientation = xlColumnField
.Position = 1
End With
Sheets("Feb 2011").Select
End Sub

when i run it for the month of march, it comes up with a run time 1004
saying UNABLE TO GET PIVOTFIELDS PROPERTY OF THE PIVOT TABLE CLASS

tried to find what this error means, but couldnot find any solotions

thanks

 

[Andrew Poulsom]

Do you have a column headed Region?

 

[wally32]

yes, the region header is under column c1

 

[Andrew Poulsom]

What code are you using to create a pivot table for a different month? The code you posted is only applicable to Feb 2011.

 

[wally32]

sorry for the delay in getting back to you. what do you mean by code? i am quite a newbie to all this. what i did is go into feb 2011 worksheet run and record a macro. went to march 2011 worksheet then re run the macro.

 

[Andrew Poulsom]

If you rerun your recorded macro it will use February's data to create the pivot table. Try changing:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Feb 2011'!C3:C7").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10

to:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & ActiveSheet.Name & "'!C3:C7").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10

 

[wally32]

ii have just run that and another run time error comes through, this time application-defined or object defined error

also

when i check a bit further down the code another error comes up at this point

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Region")
this error message states unable to get the pivotfields property of the pivottable class

think i need to go back to drawing board on this one??

 

[wally32]

would it be easier if i was to amend the recorded macro so the pivot table appears on the same worksheet instead of opening a new worksheet all the time.

 

[Andrew Poulsom]

Which line causes the error? Does that macro work on your February worksheet? If so you don't have the heading Region on the other worksheet.

 

[wally32]

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'" & ActiveSheet.Name & "'!C3:C7").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Region")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Score"), "Count of Score", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Score")
.Orientation = xlColumnField
.Position = 1
End With
Sheets("Feb 2011").Select
End Sub

i just run it again on the feb worksheet, where highlighted yellow above that is where the first error message appears "unable to get pivotfields property of the pivottable class"

each worksheet will be run of a master sheet, so all the headings will be the same

thanks