Separate one workbook w/multiple sheets into multiple workbooks

[spydey]

I have a workbook, let's call it Master, which has 7 Sheets:

Summary
Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Sheet6​

The Summary sheet has Items (A3:A100) & Location (B3:B100)

There are 62 unique Items.
There are 53 unique Locations.

There could be duplicate Items within the A3:A100 range.
There could be duplicate Locations within the B3:B100 range.

Together, an Item & a Location e.g. A3 & B3, create a unique combination that is not duplicated in the A3:B100 range. I have that range named as: SummaryArray.

Sheet1 just contains non-applicable data, but must remain intact as is.

On sheets 2 thru 6, the same Items and Locations can be found but the Item/Location (I/L) combinations can be duplicated multiple times.

For sheets 2 - 6, the number of rows varies anywhere from a few hundred to several thousand and is constantly changing as new data is added/removed.

My goal is multi fold:

1. SaveCopy the Master for each iteration of the SummaryArray I/L combos.

I think I can do it (in part) like this (Master opened):

Dim rg As Range
Dim MyArray() As Variant
Dim i As Long, j As Long
Dim Path as String
Dim filename as String

Master=ActiveWorkbook.FullName

Path="Drive:\Location\Where\I\Need\To\Save\The\Files\"


MyArray = Range("SummaryArray").Value2


For i = LBound(MyArray) To UBound(MyArray)
    For j = LBound(MyArray, 2) To UBound(MyArray, 2)
        filename= i & " - " & j[INDENT]ActiveWorkbook.SaveCopyAs FileName:=Path & filename, FileFormat:=52[/INDENT]
    Next j
Next i


End Sub

Example:
A3 shows the Item as: Carpet.
B3 shows the location as: Master Bedroom.
The save file (with macros enabled) should be named: Carpet - Master Bedroom.
A4 shows as: Cups.
B4 shows as: *******.
The save file should be named: Cups - *******.
Etc, etc.
/Example

I sense/realize/believe that there is more to it than that, but at a basic level, I think that should get me started.

After doing this, I should have approx 99 files: 1 Master & 98 combo files.

2. While the new save file for each I/L iteration is open/being generated, in the Summary sheet, remove all rows (except rows 1 & 2) that do not match the I/L combo the file is named after. Also go through sheets 2 - 6 and remove all rows (except row 1) that do not match the current SummaryArray I/L combo the file is named after.

Example: New save file for SummaryArray A5 & B5 was just created using the code from point 1 above (or something similar). Before it is closed, go through the Summary sheet, remove all rows (except rows 1 & 2) that are not the I/L combo. Also go through sheets 2 - 6 and remove all rows where the I/L combo does not match the file name I/L combo. Then save the changes to that new file (not the Master file!!!!). The end result should be that if I open that new file, on the Summary sheet I should see rows 1, 2, and 3; row three should contain the I/L combo and associated data. For sheets 2 - 6, I should only see the I/L combo (and all duplicates of the same I/L combo) that the file is named after. Sheet 1 still needs to be present in each new file. /Example

I think that something like this would at least get me pointed in the right direction:

Dim ws As Worksheet

Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.WorksheetsIf ws.Name <> "Summary" Then
If ws.Name <> "Sheet1" Then
With ws
'Delete Rows where I/L combo does not equal file name I/L combo, leaving row 1' (don't know the coding for this yet)
End With
ElseIf ws.Name = "Summary" Then
With ws
'Delete rows where I/L combo does not equal file name I/L combo, leaving rows 1 & 2 (don't know the coding for this yet)
End With
End If
Next ws
Application.ScreenUpdating = True

So I think that using something similar to those two items will start me on the right path or point me in the right direction.

I just need some help figuring out what I am lacking, where the holes are, and how to go about it best.

I think that I will need to take the 2nd coding idea and nest it somehow into the 1st coding idea, or something like that.

Perhaps I need to make a "tool" file which would open the Master file, assign it to a variable, then make changes to it as per above, saving the final results as per above, re-open the master file via the allocated variable, and do the whole process over and over again until all the items from my SummaryArray have their own files and contain only the data pertinent to them.


Hahahahaha, hopefully that all makes sense and someone will have the generosity to reach out and guide me a bit, or give me some pointers, some ideas, some input, etc.

Thanks to every for at lease taking the time to read this novel!!

Take care and cheers!!

-Spydey

 

[ajamess]

I don't have time to write out the full code, but I previously had a similar situation so I can point you in the direction of a solution that worked for me.

But instead of saving the full file, I created a template of what the save file (with columns formatted properly if needed) would be.

I would loop through the array of unique keys and filter the master sheet to the values in the current key. Then open the template, copy paste visible cells into the template, then save with the correct name. Close this new file, then go back to master and just refilter etc.

 

[ajamess]

This was at my previous job, so I don't have a copy of the code any longer. Or else I would just post an example of how I did it. Sorry. Hopefully that can give you some ideas though! Especially if large files I found it much faster to copy paste from a filter, than deleting rows. Deleting rows would often crash my machine on very large files.

 

[spydey]

This was at my previous job, so I don't have a copy of the code any longer. Or else I would just post an example of how I did it. Sorry. Hopefully that can give you some ideas though! Especially if large files I found it much faster to copy paste from a filter, than deleting rows. Deleting rows would often crash my machine on very large files.

Thank ajamess for the idea. That might be one way for me to do it.

I was hoping to stay away from something like that just because I would like to condense it all and minimize the number of files/templates/other files I would need to use. But if push comes to shove, I might have to go that route.

Thanks again!


Anyone else have any other insight, input, ideas, suggestions, assistance, etc., that they would be willing to provide?

Thanks and have a fantastic week!!

-Spydey