Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Creating a Macro to create a pivot table using a dynamic number of rows

Copper Contributor

Hi all, 

So I'm now at the very limit of my abilities ... trying to record and then debug macros - Is there anyone out there that can help? 

 

What I'm trying to accomplish is this:

 

1. Export data from my MRP (fixed number of columns, dynamic number of rows) 

2. Once exported and saved as .xlsx file, press a macro button that creates a pivot table based on the exported data

3. Replicate this to another team members excel giving him the ability to create this report at the touch of a button 

 

This is how far I've got:

 

1. exported the data into .xlsx file

2. press record macro to "this workbook" 

3. manually create the pivot table in the exact way that I want it to be completed

4. stop the recording 

 

Where I run into problems ....

 

I then delete the pivot table that I've just created and attempt to run the macro on the same data just to make sure that it works .... and it doesn't. 1st hurdle. I keep getting macro run errors which I don't understand.

 

I then anticipate that as soon as I've overcome this, I'll have the 2nd hurdle where the data today will not be same as it is tomorrow so the macro needs to be able to cope with varying rows and varying. 

 

Can anyone help me? 

 

I can provide a sample of the raw data and the pivot table that i'm trying to achieve? 

 

Thank you 

3 Replies

This is the initial error I'm getting highlighted yellow in the VBA editor

 

Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"GridData!R1C1:R443C21", Version:=6).CreatePivotTable TableDestination:= _
"Sheet1!R3C1", TableName:="PivotTable3", DefaultVersion:=6

Were you ever able to solve it? Im facing the exact same problem and its driving me crazy @RichTBennett 

Have you considered converting the source data to a table and pivoting off the table? All you'd need to update the pivot would be to right-click it and choose refresh. If you really wanted to take it further, you create a macro that simply refreshes the pivot table at intervals.

 

Another option could be the forthcoming PIVOTBY function...