westjelly
Board Regular
- Joined
- Jul 5, 2005
- Messages
- 50
Hi,
I'm trying to figure out how to automate a rather time-intensive process and have, as yet, been unable to crack exactly what I need.
What I have:
- MasterData workbook (1 sheet)
- Template (2 sheets - 1st sheet does a bunch of vlookups, calculations on 2nd sheet - data)
I need to selectively copy the data in the masterdata workbook into the template in ever-more specific selections.
The filters I'll be working with are:
1 - Group
2 - Level1
3 - Level2
4 - Level3
5 - Level4
So, right now, I manually:
1. filter for a group.
2. Select the data (B5 to the last row in column DZ)
3. Copy it into the template workbook (the "data" sheet) at Cell B5.
4. "Save as" the template with a naming convention MMDDYY Group File Q3
5. Hit Delete to clear the pasted data
6. Go back to the other sheet and return to step 1, until all groups have been divided.
The next step (you see where this is going) is to further divide. So, I filter by group, THEN by Level1. From there, I copy the data, saving the result as MMDDYY Level1 Group Q3
Once all those are done, I move on Filter by Group, Level1, and Level2
Then by Group, Level1, Level2, and Level3
You can see the redundancy... They all come from the same single worksheet of data.
So, what I'd like to do is run a macro once I have the MasterData sheet finished, which would step through filtering, copying and saving.
One of the gravy aspects:
The template is in 3 versions, depending on how much data will likely be used. Ideally, I would have a macro autoselect which template to use, depending on how many rows of filtered data I'm copying over. Anywhere from 300, to 3000, to 30000 or so. The smaller files calculate quicker.
-or-
Gravy #2
Along the process, any brainstorms on how I could use a single template, but keep it from calculating 30000 rows if there are only 10 or something?
I'm trying to figure out how to automate a rather time-intensive process and have, as yet, been unable to crack exactly what I need.
What I have:
- MasterData workbook (1 sheet)
- Template (2 sheets - 1st sheet does a bunch of vlookups, calculations on 2nd sheet - data)
I need to selectively copy the data in the masterdata workbook into the template in ever-more specific selections.
The filters I'll be working with are:
1 - Group
2 - Level1
3 - Level2
4 - Level3
5 - Level4
So, right now, I manually:
1. filter for a group.
2. Select the data (B5 to the last row in column DZ)
3. Copy it into the template workbook (the "data" sheet) at Cell B5.
4. "Save as" the template with a naming convention MMDDYY Group File Q3
5. Hit Delete to clear the pasted data
6. Go back to the other sheet and return to step 1, until all groups have been divided.
The next step (you see where this is going) is to further divide. So, I filter by group, THEN by Level1. From there, I copy the data, saving the result as MMDDYY Level1 Group Q3
Once all those are done, I move on Filter by Group, Level1, and Level2
Then by Group, Level1, Level2, and Level3
You can see the redundancy... They all come from the same single worksheet of data.
So, what I'd like to do is run a macro once I have the MasterData sheet finished, which would step through filtering, copying and saving.
One of the gravy aspects:
The template is in 3 versions, depending on how much data will likely be used. Ideally, I would have a macro autoselect which template to use, depending on how many rows of filtered data I'm copying over. Anywhere from 300, to 3000, to 30000 or so. The smaller files calculate quicker.
-or-
Gravy #2
Along the process, any brainstorms on how I could use a single template, but keep it from calculating 30000 rows if there are only 10 or something?