workonexcel1
New Member
- Joined
- Aug 18, 2024
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
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.![Smile :) :)](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png)
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.
![Smile :) :)](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f642.png)