Saving several hidden worksheets

Question

I found and adjusted code that saves all worksheets (in 1 Excel file) to separate workbooks in a specific folder and then deletes the worksheet from existing workbook so in the end instead of 12 worksheets in 1 workbook I have 12 workbooks with 1 worksheet in 1 folder.

I would like to save 4 hidden worksheets and macros so in the end I would like to have 12 workbooks that contains of 1 visible sheet (called Sheet1), 4 hidden sheets (called: Lookups, Copy Data, Export Data, Preview) and macro so in the end I would have 12 workbooks (.xlsm) with 5 worksheets and with macro enabled. The number of visible worksheets varies but the hidden worksheets are always the same.

My code:

Sub sheets_to_files(Optional byDummy As Byte)

Dim xPath As String
Dim ws As Worksheet
xPath = Application.ActiveWorkbook.path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each xWs In ThisWorkbook.Sheets
  xWs.Copy
  Application.ActiveWorkbook.SaveAs FileName:=xPath & "\" & xWs.Name & 
  ".xlsm", FileFormat:=52
Application.ActiveWorkbook.Close False
Next

For Each ws In Worksheets
    Select Case ws.Name
        Case "Sheet1"
        Case Else
            ws.Delete
    End Select
Next ws
Application.DisplayAlerts = True
End Sub

I believe that I would need to create an array and change these lines

For Each xWs In ThisWorkbook.Sheets
    xWs.Copy

What is the source of the sheets intended to be Hidden? Are they created in the new workbook? — Cyril
the hidden sheets are in the active excel file, I don't want to create them, I just want to copy them to each new created workbook — Adrian

Cyril Cyril · Accepted Answer · 2017-10-11T14:23:10

In general, you can use the simple Sheets("NAME").Move to put a sheet to a new workbook.

Dim i As Long, j As String, k As Workbook, xPath As String
xPath = Application.ActiveWorkbook.path
Set k = ThisWorkbook
If k.Sheets.Count > 1 Then 'Check
    For i = k.Sheets.Count To 2 Step -1
        j = k.Sheets(i).Name
        k.Sheets(i).Move
        'ActiveWorkbook.Sheets.Add.Name = "Test" 'Lookups, Copy Data, Export Data, Preview
        'ActiveWorkbook.Sheets("Test").Visible = xlSheetHidden
        ActiveWorkbook.SaveAs FileName:=xPath & "\" & j & ".xlsm", FileFormat:=52
        ActiveWorkbook.Close
    Next i
End If

Saving several hidden worksheets

1 Answers