0

I was working with the following VBA (attached below). It works great, and what I like the most is that it is Path/Name independent. I am now trying to save multiple reports which are based on some HiddenSheets (very hidden), which I don't want to include. The problem with this Macro is that it creates individual Worksheets for every sheet, including the invisible sheets. Is there a chance to have the whole same concept of creating a folder with the same workbook name but only include the visible sheets? If so, please let me know. I have been trying to tweak this code so much, and it either includes only one sheet or all of them (not the visible only) Thanks!

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
            Cells.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
1

While a hidden worksheet can be xlSheetHidden (0) or xlSheetVeryHidden (2), if it is visible it can only be xlSheetVisible (-1). You can check the Worksheet.Visible property and determine whether you want to proceed or not.

As a shortcut, if you copy a worksheet without providing a destination then you create a new copy of the original workbook with a single worksheet.

Option Explicit

Sub SaveShtsAsBook()
    Dim Sheet As Worksheet, MyFilePath As String, w As Long

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    On Error Resume Next '<< a folder exists

    'define the workbook
    With ThisWorkbook

        MyFilePath = Left(.FullName, InStrRev(.FullName, ".") - 1)
        MkDir MyFilePath '<< create a folder

        For w = 1 To .Worksheets.Count

            'check if worksheet is visible
            If .Worksheets(w).Visible = xlSheetVisible Then

                .Worksheets(w).Copy   'create new active workbook with copy of worksheet

                With ActiveWorkbook
                    .Worksheets(1).Cells(1, "A").Select
                     'save book in this folder. Use FileFormat argument instead of adding .XLSX
                    .SaveAs Filename:=MyFilePath & "\" & .Worksheets(1).Name, FileFormat:=xlOpenXMLWorkbook
                    'close saving changes
                    .Close SaveChanges:=True
                End With

            End If

        Next w

    End With

    Sheet1.Activate

End Sub
|improve this answer|||||
  • Thanks it did the trick! – Pepe Jan 22 at 21:09
0

Skip the sheets where Worksheets("Sheet1").visible = False

|improve this answer|||||
  • The sheets (I want to exclude are already hidden) yet the macro above would still create individual workbooks (including Hidden/VeryHidden sheets) – Pepe Jan 22 at 20:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.