CODEWITHSUNDEEP
vbaexcel
Adrian
Adrian

Reputation: 907

Saving several hidden worksheets

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

Upvotes: 0

Views: 93

Answers (1)

Cyril
Cyril

Reputation: 6829

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

Upvotes: 0

Related Questions