Macro/VBA code code for creating new excel file for each pivot table from a column filter

[workonexcel1]

HI Everyone, Thanks in advance. I haven't done any excel automation/macro work. Need to do repetitive manual step wish to take your help to automate.

Have an excel with multiple columns and 1000s of rows.
First part
1) I have a big master excel. I have ~100 terms available that i need to put in filter 1 by 1 in column D.

2) Once a term is filtered in column D, the filtered list I do Alt + ; (to only copy filtered table) and then copy ('ctrl + c') the filtered table , create a new excel file (ctrl+n) save it with a name of the term I filtered column D with above this time and then paste in this new excel.

3)Then in this new excel I select the pasted table and then I click on insert pivote table with 'add this data to data model' ticked.

4)It opens a new tab with blank pivot table. I name new tabe as 'pivot table 1'.

5)In Pivot table I right click 'options' -> 'display' and select 'classic pivot table layout' .

6)On the 'Pivot table field' right hand side, I choose few fields (~10) then drag in to 'rows' one by one in a particular order.
----
(second part)
7) in this new filled up Pivot table then on the 4 column i choose filter 1 by 1 (approx 5 unique terms).

8) then copy this filtered (4th column) pivot table using 'Alt +;' then 'ctrl +c' to only copy filtered table.

9) Open a new Tab in this same excel pivot table and rename it the term i filtered with in step 7.

10) I repeat this ~ 5 times (number of options available in fourth coumn) and create ~5 new tabs.
---

Then i repeat from first step(then second) ~100 times for each term i search in column D of master excel.

So i create ~100 new excel from 1 main excel each have ~5/6 tabs.


Need help with a code/macro for this process.
Looking if this kind forum can help with the code to auomate this flow.

Thank you so much for reading.

 

[Anthony47]

I think you may automatize steps 1-6 by using a "slave" workbook, with a list of data in Sheet1 and the desired pivot table already configured in Sheet2; then, from your main workbook:
-filter column D
-Select and Copy the filtered columns
-Select the slave Workbook, Sheet1, Range A1, and Paste the copied values
-select a cell in the pivot table (Sheet2), rightclick, chose "Update"

This should result in the pivot table ready for you based on the filtered data

This task can easily be automatized using a self recorded macro that executes the above steps
For more information on how recording a macro: Automate tasks with the Macro Recorder - Microsoft Support

Self recorded macro would help also on the phase two

But I think you should explore how Excel features (slicers, for example) and high level functions (like FILTER, UNIQUE, LET and much more) could let you do the job in different way
Together with self recorded macro you could reach a high level of automation

For a first view of the SLICER I suggest you look at this: Excel Slicers - Introduction, what are they, how to use them, tips, advanced concepts, interactive charts & reports using Slicers & Pivot Tables