CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1456

VBA Excel hide sheets with some name when printing PDF

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

enter image description here

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?

Upvotes: 2

Views: 456

Answers (3)

Dy.Lee
Dy.Lee

Reputation: 7567

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

Upvotes: 1

snenson
snenson

Reputation: 436

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

Upvotes: 2

VBasic2008
VBasic2008

Reputation: 54797

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

Upvotes: 0

Related Questions