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

[RichTBennett]

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 

[#2]

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

[#3]

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

[#4]

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...