Thread: Macro to create a pivot table
2014-08-29, 18:09 #1
- Join Date
- Jan 2001
- Las Vegas, Nevada, USA
- Thanked 0 Times in 0 Posts
Macro to create a pivot table
I have recorded a macro to create a pivot table from data exported from one of our applications. The macro would select the Report Filter, Row Labels and Values. I carefully stepped through my notes while recording and now, in looking at the code I realize I will be stuck in only the second line of code.
I preselected the range of data and fields title row but Excel has converted the range to SourceData:= "~PD1BE9!R7C1:R93236C22"
My second problem will be name of the pivot table. In recording the macro Excel has assigned the code: TableName:="PivotTable3"
How can I handle the data range and pivot table name so that I can reuse the code in another worksheet?
Subscribe to our Windows Secrets Newsletter - It's Free!
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2014-08-30, 03:42 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 331 Times in 324 Posts
At runtime you could determine the number of pivottables in the workbook:
x = 0
For each wks in Activeworkbook
x = x + wks.PivotTables.Count
x = x+1
and then in your creation code use something like:
TableName:="PivotTable" & x,
For the source you could use something like: