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

%3CLINGO-SUB%20id%3D%22lingo-sub-2001324%22%20slang%3D%22en-US%22%3ECreating%20a%20Macro%20to%20create%20a%20pivot%20table%20using%20a%20dynamic%20number%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2001324%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20I'm%20now%20at%20the%20very%20limit%20of%20my%20abilities%20...%20trying%20to%20record%20and%20then%20debug%20macros%20-%20Is%20there%20anyone%20out%20there%20that%20can%20help%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20I'm%20trying%20to%20accomplish%20is%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Export%20data%20from%20my%20MRP%20(fixed%20number%20of%20columns%2C%20dynamic%20number%20of%20rows)%26nbsp%3B%3C%2FP%3E%3CP%3E2.%20Once%20exported%20and%20saved%20as%20.xlsx%20file%2C%20press%20a%20macro%20button%20that%20creates%20a%20pivot%20table%20based%20on%20the%20exported%20data%3C%2FP%3E%3CP%3E3.%20Replicate%20this%20to%20another%20team%20members%20excel%20giving%20him%20the%20ability%20to%20create%20this%20report%20at%20the%20touch%20of%20a%20button%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20how%20far%20I've%20got%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20exported%20the%20data%20into%20.xlsx%20file%3C%2FP%3E%3CP%3E2.%20press%20record%20macro%20to%20%22this%20workbook%22%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20manually%20create%20the%20pivot%20table%20in%20the%20exact%20way%20that%20I%20want%20it%20to%20be%20completed%3C%2FP%3E%3CP%3E4.%20stop%20the%20recording%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20I%20run%20into%20problems%20....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20then%20delete%20the%20pivot%20table%20that%20I've%20just%20created%20and%20attempt%20to%20run%20the%20macro%20on%20the%20same%20data%20just%20to%20make%20sure%20that%20it%20works%20....%20and%20it%20doesn't.%201st%20hurdle.%20I%20keep%20getting%20macro%20run%20errors%20which%20I%20don't%20understand.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20then%20anticipate%20that%20as%20soon%20as%20I've%20overcome%20this%2C%20I'll%20have%20the%202nd%20hurdle%20where%20the%20data%20today%20will%20not%20be%20same%20as%20it%20is%20tomorrow%20so%20the%20macro%20needs%20to%20be%20able%20to%20cope%20with%20varying%20rows%20and%20varying.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20anyone%20help%20me%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20provide%20a%20sample%20of%20the%20raw%20data%20and%20the%20pivot%20table%20that%20i'm%20trying%20to%20achieve%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2001324%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2001341%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20a%20Macro%20to%20create%20a%20pivot%20table%20using%20a%20dynamic%20number%20of%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2001341%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20the%20initial%20error%20I'm%20getting%20highlighted%20yellow%20in%20the%20VBA%20editor%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheets.Add%3CBR%20%2F%3EActiveWorkbook.PivotCaches.Create(SourceType%3A%3DxlDatabase%2C%20SourceData%3A%3D%20_%3CBR%20%2F%3E%22GridData!R1C1%3AR443C21%22%2C%20Version%3A%3D6).CreatePivotTable%20TableDestination%3A%3D%20_%3CBR%20%2F%3E%22Sheet1!R3C1%22%2C%20TableName%3A%3D%22PivotTable3%22%2C%20DefaultVersion%3A%3D6%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 

1 Reply

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