Results 1 to 2 of 2
Thread: Macro to create a pivot table
2014-08-29, 19: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?
2014-08-30, 04:42 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 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: