Script to copy selected filtered Excel pivot table columns to another excel tab?

Brass Contributor



I have been filtering different views of my base Pivot table (based on the 4 different Age categories present in my dataset) and copying selected columns over to a new Excel tab to show the result in a small table for that age group.  I do this workflow for each of the Age categories, so the new Excel tab ultimately has 4 tables (one for each Age category).  This is a highly manual process prone to error.


Once I've refreshed my pivot table with the next Age category filter, is there a script that would then select the 3 columns I'm interested in and copy them over into consecutive column (so as to form a table)? THEN - I want to go back to the Pivot table, (manually) refresh the results for the next Age Category, use the script to select the same 3 columns and copy it over to the Excel tab that has the previous table(s) I've already copied over, and create the new table next to the previously-created table (with one blank column in-between).  Here's a screenshot of what the output results would look like:




So, once the Pivot table has been manually refreshed to show the Age Category I want, my thought is to execute the script to select the same 3 columns and copy it over to the Excel tab in adjacent tables as shown above.  Can this be done to take some of the manual parts of the process and put them in a script as described?  Thank you!

1 Reply
Why not just copy the entire pivot table twice and filter each of them accordingly? That way you only have to do this copy once. After that, a refresh all suffices.