Macro to create two pivots on the same page

Originally Posted by TMShucks

Please post a sample workbook with some typical data and the code as above.

Regards

Hello TM Shucks, I hope you had a good weekend. Thank you for your offer to help me sir, much appreciated. Please find attached a copy of the sheet containing the macro. In summary, the macro is supposed to create a new sheet and add two pivot tables, however the code falls over at the first stages (before the pivot can be created). As mentioned I have tried declaring variables, downsizing the criteria ranges but all without any success.


Many thanks sir

Ivor

Originally Posted by TMShucks

See the attached updated workbook.

Pivot Table coding is not my strong point so it's not ideal in that cells are selected ... but it seems to work and you can re-run it successfully.

Assuming that it produces the results that you expect, it's now working ;-)

Please Login or Register  to view this content.

Regards

Hello Sir

I don't know how you did it, but you did it!!! Thank you so very much. Pivots do appear to be an unknown entity when it comes to coding. Luckily for me the pivots will not change, so I can now go ahead and add in code to auto refresh the totals after each run. I am intrigued to know how you knew what to change to get the code to work? I will compare your code against my recorded one for the answer. I do need to add on one more pivot on a separate page, but I guess I can utilise the code you have given me to create that one also.

Thanks again sir for your help, this is remarkable.


Cheers
Ivor

Originally Posted by TMShucks

You're welcome. Thanks for the feedback and the rep.

The critical part is this:

Please Login or Register  to view this content.

And then referring to newSheet rather than ActiveSheet wherever it was used. I'm sure there is a better way. When you add a sheet, it becomes the active sheet so, while you have hold of it, assign it to a variable that you can keep track of. Your code refers to Sheet1 which was fine when you first ran it but you get errors second time through.

Anyway, it works, although it could probably be refined with some time and effort.

Regards

Brilliant, and thanks again sir. There are some clever people in this company, they know Java, Agile and so on.... but no one here in the company knows about using VBA on pivots, so with you last bit of advice, I have something valuable against my knowledge (even though I stole it from you). Thanks again my man and no doubt I'll be on here asking for help again real soon, my spreadsheet is not yet complete....ha ha.

Have a good day sir

Cheers
Ivor