VBA Excel hide sheets with some name when printing PDF

Question

I have a problem with sheet hiding. I want to print the document, which includes the sheets with a similar name.

I did something like this:

  Sub DPPtoPDF()

  Dim ws As Worksheet

  Sheets("Readme").Visible = False
  Sheets("Asbuilt Photos 1").Visible = False
  Sheets("Asbuilt Photos 2").Visible = False
  Sheets("Splicing Photos").Visible = False
  Sheets("Sign Off Sheet").Visible = False

  For Each ws In ThisWorkbook.Worksheets
  If ws.Name Like "OTDR*" Then
  ws.Visible = False
  End If

  Next was
  ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=True

  Sheets("Readme").Visible = True
  Sheets("Asbuilt Photos 1").Visible = True
  Sheets("Asbuilt Photos 2").Visible = True
  Sheets("Splicing Photos").Visible = True
  Sheets("Sign Off Sheet").Visible = True

  For Each ws In ThisWorkbook.Worksheets
  If ws.Name Like "OTDR*" Then
  ws.Visible = True
  End If
  Next was
  Sheets("Frontsheet").Select

  End Sub

following the solutions:

https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.visible

Unhide sheets names using VBA whose name contain specific word

https://excelchamps.com/vba/hide-sheet/

it returns over 2000 pages in my PDF document

What have I done wrong in the code?

I have about 30 sheets at most. How can I exclude (hide) the following sheets from printing it as the PDF?

Do you want the printsettings to be so? Paper format, margins etc? — snenson
– snenson, Commented Mar 24, 2021 at 14:00

snenson · Accepted Answer · 2021-03-24 12:37:42Z

2

I would suggest to do it with the print function. Here is my code:

Sub DPPtoPDF()

  Dim ws As Worksheet

  Sheets("Readme").Visible = False
  Sheets("Asbuilt Photos 1").Visible = False
  Sheets("Asbuilt Photos 2").Visible = False
  Sheets("Splicing Photos").Visible = False
  Sheets("Sign Off Sheet").Visible = False

  For Each ws In ThisWorkbook.Worksheets
  If ws.Name Like "OTDR*" Then
  ws.Visible = False
  End If

  Next ws
  
  
  Dim arr As Variant
  Dim i As Integer
  Dim counter As Integer
  ReDim arr(counter)
  'Add all visible sheets to an array (arr)
  For i = 1 To Worksheets.Count
    If Worksheets(i).Visible = True Then
        ReDim Preserve arr(counter)
        arr(counter) = Worksheets(i).Name
        counter = counter + 1
    End If
  Next
  'select all sheets the array contains
  Worksheets(arr).Select
  'set the path
  printpath = ThisWorkbook.Path & "\" & ThisWorkbook.Name
  'print the selection
  ThisWorkbook.Windows(1).SelectedSheets.PrintOut PrintToFile:=True, PrToFileName:=printpath, ActivePrinter:="Microsoft Print to PDF", IgnorePrintAreas:=True

  Sheets("Readme").Visible = True
  Sheets("Asbuilt Photos 1").Visible = True
  Sheets("Asbuilt Photos 2").Visible = True
  Sheets("Splicing Photos").Visible = True
  Sheets("Sign Off Sheet").Visible = True

  For Each ws In ThisWorkbook.Worksheets
  If ws.Name Like "OTDR*" Then
  ws.Visible = True
  End If
  Next was
  Sheets("Frontsheet").Select

End Sub

answered Mar 24, 2021 at 12:37

snenson

4361 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Geographos Over a year ago

It says, that it's not enough memory to finish the print. It still tends to print over 2000 pages.

snenson Over a year ago

Then try to put "For i = 0 To UBound(arr) Debug.Print arr(i) Next" after the for loop which i added an look in the debug area it will show you all the sheets it tries to print.

Geographos Over a year ago

It doesn't work either: If Worksheets(i).Visible = True Then subscript out of range

snenson Over a year ago

But does it show you the workbooks? Or does it stop a the if Worksheets(i).visible=true line?

Geographos Over a year ago

I found an error, which wasn't related to the situation. Thanks for help. Your code is good.

Dy.Lee · Accepted Answer · 2021-03-24 13:41:47Z

1

You just need to select multiple sheets and make only the selected sheets into a pdf file.

Sub DPPtoPDF()

    Dim ws As Worksheet
    Dim vName() As Variant
    Dim n As Integer

    ReDim vName(1 To 1000)
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name Like "OTDR*" Or ws.Name = "Readme" Or ws.Name = "Asbuilt Photos 1" _
          Or ws.Name = "Asbuilt Photos 2" Or ws.Name = "Splicing Photos" _
          Or ws.Name = "Sign Off Sheet" Then
        Else
          n = n + 1
          vName(n) = ws.Name
        End If
    Next ws
    ReDim Preserve vName(1 To n)
    Sheets(vName).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        ThisWorkbook.Path & "\" & "test.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

answered Mar 24, 2021 at 13:41

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

4 Comments

Geographos Over a year ago

Still the same, I am getting 2084 pages exactly. The same situation as with my code and answers above.

Dy.Lee Over a year ago

@MKR, "OTDR*" to " ~*OTDR~* " . Does the sheet name begin with OTDR or is it included in the sheet name?

Geographos Over a year ago

It starts from OTDR

Dy.Lee Over a year ago

@MKR, Can you post a screenshot of the sheet tab so that the sheet name is visible?

VBasic2008 · Accepted Answer · 2021-03-24 12:31:33Z

0

Print Specified Worksheets

Option Explicit

Sub DPPtoPDF()

    Const SheetNamesList As String = "Readme,Asbuilt Photos 1," _
        & "Asbuilt Photos 2,Splicing Photos,Sign Off Sheet"
    Const crit As String = "OTDR*"
    
    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim SheetNames() As String: SheetNames = Split(SheetNamesList, ",")
    
    Dim sh As Object
    Dim snms() As String
    Dim shName As String
    Dim n As Long
    
    For Each sh In wb.Sheets
        shName = sh.Name
        If Not UCase(shName) Like crit Then
            If IsError(Application.Match(shName, SheetNames, 0)) Then
                 ReDim Preserve snms(n)
                 snms(n) = shName
                 n = n + 1
            End If
        End If
    Next sh
    
    ' Copy to new workbook
    wb.Worksheets(snms).Copy
    
    With ActiveWorkbook
        .ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=wb.Path & "\" & wb.Name, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
        .Close False
    End With
    
    wb.Sheets("Frontsheet").Select

End Sub

answered Mar 24, 2021 at 12:31

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

7 Comments

Geographos Over a year ago

Instead of PDF I am receiving a new Excel book including the sheets considered.

VBasic2008 Over a year ago

Still working on my end. If the workbook name is test.xlsm it will create the file test.xlsm.pdf (Office 2019). Maybe you should use Left(wb.Name, InstrRev(wb.Name, ".") - 1) instead of wb.Name to get test.pdf.

Geographos Over a year ago

Nope, still the same mate.

VBasic2008 Over a year ago

Here's a link to my file on Google Drive. You have to download it (down arrow on the top right).

Geographos Over a year ago

See in the lik what Iam getting, also from your file: imgur.com/a/1cO6w4H Maybe in my Excel settings is something wrong?

|

Collectives™ on Stack Overflow

VBA Excel hide sheets with some name when printing PDF

3 Answers 3

5 Comments

4 Comments

Print Specified Worksheets

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

4 Comments

Print Specified Worksheets

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related