excel macro to create pivottable

wally32

New Member
Joined
Mar 7, 2011
Messages
29
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Mar 7, 2011
Messages
29

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If you rerun your recorded macro it will use February's data to create the pivot table. Try changing:

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

to:

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

wally32

New Member
Joined
Mar 7, 2011
Messages
29

ADVERTISEMENT

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

New Member
Joined
Mar 7, 2011
Messages
29
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Mar 7, 2011
Messages
29
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,400
Messages
5,601,462
Members
414,451
Latest member
jrose7

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top