Use a Loop to Copy Certain Sets of Sheets to New Workbooks

[embellishedego]

So here's my brain-killer:

I need to save a Glossary tab with each of my worksheets (Plant Code Tabs) in this workbook to send out files to managers. I have the code that creates the individual Plant Code files and saves them to my desktop, using the plant codes for the new workbook name. Can anyone give me a hand here with adding a segment to my loop to add the glossary tab to my new workbooks?

Also, can I use an "else" to only create specific files if I don't want files of every single tab? I'm new to VBA, and haven't really figured out how to declare and specify details with loops yet. Thank you!



Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Sheets(Array("ActiveSheet", "Glossary of Terms")).Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
'save book in this folder
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xlsx"
.Close savechanges:=True
End With
.CutCopyMode = False
Next
End With
Sheet1.Activate
End Sub



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

Can you please rephrase that a bit. I am not able to figure out what exactly are you trying to get done

[embellishedego]

Excel Fox, thank you for the quick response! I'll back up a bit; my workbook has a summary tab with all of my data in it. The data is filtered by plant code (ex. BG, CW), and then pasted on a tab labeled "BG" or "CW." I am trying to save two sheets together in a new workbook: a Glossary/Instructions type tab, and the corresponding plant code tab (BG). I want the sheet to be named after the plant code, as it currently is, but have both sheets instead of just the plant code sheet (individual tab).

Now: BG.xlsx is saved with only one tab, the BG filtered data
Need: BG.xlsx saved with two tabs, the BG filtered data, as well as the Glossary tab.

In summary, the code above loops through my workbook and creates separate workbooks for every single tab, saving them in a destination folder. I want to include the other tab "Glossary" in with these single workbooks as well. How doable is that?

[Excel Fox]

Pretty doable. And this glossary tab should be empty? Cause I don't see you mentioning anywhere what the glossary tab will contain.

[Excel Fox]

Try this



Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
MyFilePath$ = ActiveWorkbook.Path & "\" & _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
With Application
.ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For N = 1 To Sheets.Count
Sheets(N).Activate
SheetName = ActiveSheet.Name
Sheets(Array("ActiveSheet", "Glossary of Terms")).Copy
Workbooks.Add (xlWBATWorksheet)
With ActiveWorkbook
With .ActiveSheet
.Paste
.Name = SheetName
[A1].Select
End With
.Worksheets.Add After:=.Worksheets(1)
.Worksheets(2).Name = "Glossary"
'save book in this folder
.SaveAs Filename:=MyFilePath _
& "\" & SheetName & ".xlsx"
.Close savechanges:=True
End With
.CutCopyMode = False
Next
End With
Sheet1.Activate
End Sub