Results 1 to 5 of 5

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

  1. #1
    Junior Member
    Join Date
    Mar 2017
    Posts
    2
    Rep Power
    0

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

    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!


    Code:
    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
    Last edited by DocAElstein; 07-07-2023 at 12:59 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Can you please rephrase that a bit. I am not able to figure out what exactly are you trying to get done
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Mar 2017
    Posts
    2
    Rep Power
    0
    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?

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Pretty doable. And this glossary tab should be empty? Cause I don't see you mentioning anywhere what the glossary tab will contain.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try this

    Code:
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 2
    Last Post: 03-09-2015, 11:26 PM
  2. setting loop code to check all named sheets
    By peter renton in forum Excel Help
    Replies: 9
    Last Post: 01-30-2014, 03:01 AM
  3. Replies: 3
    Last Post: 05-14-2013, 03:25 PM
  4. Search word in different workbooks and sheets
    By k0st4din in forum Excel Help
    Replies: 5
    Last Post: 11-29-2012, 10:10 PM
  5. Replies: 2
    Last Post: 11-08-2012, 01:15 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •