Save multiple files with 3 worksheets

  • Hi,


    I need a help here ... if it's possible ....[Blocked Image: https://www.excelforum.com/images/smilies/frown.gif]

    I have a file with 100 worksheets

    worksheet1 - "Master"

    worksheet2 - "Sheet2"

    worksheet3 - "Instructions"

    worksheet4 - "Headers"

    worksheet5 - "tabel1"

    worksheet6 - "tabel2"

    worksheet7 - "tabel3"

    .......

    worksheet100 - "tabel96"


    I use below code:



    Code:

    Code
    1. Sub SplitEachWorksheet()
    2. Dim FPath As String
    3. FPath = Application.ActiveWorkbook.Path
    4. Application.ScreenUpdating = False
    5. Application.DisplayAlerts = False
    6. For Each ws In ThisWorkbook.Sheets ws.Copy Application.ActiveWorkbook.SaveAs Filename:=FPath & "\" & ws.Name & ".xlsx" Application.ActiveWorkbook.Close False
    7. Next
    8. Application.DisplayAlerts = True
    9. Application.ScreenUpdating = True
    10. End Sub

    it works, the code will create 96 files (named after the worksheet name) only with 1 worksheet.

    I don't know how to change the code it but I need to create 96 separately file with 3 worksheets:


    file1:

    worksheet1 - "Instructions"

    worksheet2 - "Headers"

    worksheet3 - should be named "Items" - with the info from "tabel1"


    file2:

    worksheet1 - "Instructions"

    worksheet2 - "Headers"

    worksheet3 - should be named "Items" - with the info from "tabel2"


    ...........


    file96

    worksheet1 - "Instructions"

    worksheet2 - "Headers"

    worksheet3 - should be named "Items" - with the info from "tabel96"


    Thank you!

  • Try

  • Copied the wrong code before (previous post).

    This should do the trick.

  • Hi, thank you for the code, but I missed something important! My fault!

    The name of worksheets are different (such BTYD, TGUM, RCDA, etc... they could be maximum 150 wks. The names of the new files should be BTYD.xlsx, TGUM.xlsx, RCDA.xlsx, etc... and they need contains 3 wks identically: Headers, Instructions and Items (the information in Items in first new file should be info from wks BTYD and so on...)

    I realize this thing when I put the code:

    Sheets(Array("Instructions", "Headers", "tabel" & i)).Copy but I don't have such a wks with name "tabel" & i


    I really appreciate your effort!

    Thanks again!

  • Try this.