CODEWITHSUNDEEP
excelpivot-tablevba
Bulldoggrad
Bulldoggrad

Reputation: 1

Excel Pivot Table macro to use on multiple worksheets with dynamic ranges

I create a report every month at work that parses sales out by product family. Due to the limitations within the report I use for the data, I can't simply use a pivot table to get the data I need for my report. I slice the data into separate worksheets by product family. The number of rows on each sheet vary depending upon the number of customers that bought product in that product family. I currently have a macro that I run each time I create a new sheet that does sums, sumproduct and sumif procedures. I want to also add a pivot table at the bottom of the data on each sheet for further manipulation.

I have no idea what I'm doing wrong, but my code isn't working. Here it is:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "ActiveSheet.Name!R1C1:R231C30", Version:=6).CreatePivotTable TableDestination:= _
    "ActiveSheet.Name!R237C3", TableName:="PivotTable21", DefaultVersion:=6
Sheets("ActiveSheet.Name").Select
Cells(237, 3).Select

Upvotes: 0

Views: 898

Answers (1)

Tim Williams
Tim Williams

Reputation: 166391

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "'" & ActiveSheet.Name & "'!R1C1:R231C30", Version:=6).CreatePivotTable TableDestination:= _
    "'" & ActiveSheet.Name & "'!R237C3", TableName:="PivotTable21", DefaultVersion:=6

Sheets(ActiveSheet.Name).Select 'Seems redundant here?
Cells(237, 3).Select

You need the single quotes in case the sheet name has spaces.

Upvotes: 0

Related Questions