0

I have a requirement to create 50 'base' pivot tables out of the same data sheet, so was hoping to write a VBA script to create 50 worksheets and the pivot tables for each. I tried to record a macro capturing the first 5 or so, but when I tried to run it, it crashed due to length of script. (I don't have the error message anymore but can reproduce if needed.) Can this be done with a loop? And if so, can someone please help? Thanks.

Improve this question

1 Answer 1

Reset to default
1

You could simplify things by using code to create 1 new sheet with a base pivot table, then copy that sheet 49 times. From that point, it wouldn't be too difficult to rename the sheets and reference the respective pivot tables using additional code, especially if there's only a single pivot table per sheet.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks - will give this a try.
Glad to hear it! One more thing, if you are displaying the same exact fields in each of the 50 pivot tables AND each pivot table is just filtered differently, you might want to consider creating a single pivot table with "slicers" to quickly filter the data. I have gotten into the habit of taking this approach whenever asked to provide separate views into the same data...and more often then not, people are pleased with the results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.