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]

You can't combine the 2 procedures. Surely you don't want to create a pivot table each time there is a change in a cell in column C?

 

[wally32]

basically just need column C (region column) to format automatically when user enters the information. would use the conditonlly format option in excel but you can only do 3 formats, where as i need 5.

the pivottable will only be created at the end of each month.

so for the worksheet there be 2 macros, one just to format cells daily and one to analysis info at the end of month.