Creating two pivot tables on same sheet - Optimize recorded macro code

Hi all,
I am trying to record a macro to create two pivot tables on the same sheet.
I need your suggestions to optimize the code.
Also, there are two issues that I'll like to get sorted out
1. The macro always throws an error at the point where the pivot table is being inserted.
2. The number of rows vary every month and I don't know how to optimize the code to accordingly.

Please find attached the raw data and code for your reference.

Please Login or Register  to view this content.

Thanks and regards,
johnny_tc

Hi, johnny_tc,

you use the constants PivotTable8 and PivotTable9 for thenames of the pivot tables. As long as these pivot tables still are in the workbook an exception should be trhown up.

Alternative: use constants for the names of the pivottables and alter the code where the old constants are used.

Regarding the varying ranges of data:

Please Login or Register  to view this content.

Ciao,
Holger

Thanks a lot Holger. I will have a look at this code and will let you know of the progress.

Meanwhile there is one more issue that I'm facing.
While trying to insert the second pivot table, it is automatically taking the Grand Totals at the bottom.
Is there any way to avoid this while inserting the second pivot???

Hi, johnny_tc,

first of all the sheetname should be altered to "'Raw Data'" instead of "Raw Data".

Second: if you know the name of the PT as you have passed it in (like in the example):

Please Login or Register  to view this content.

Ciao,
Holger