VBA create list with specific number of worksheets to print-CodePudding

I have never really worked with VBA and I am getting stuck on a simple problem. Essentially I have a workbook with multiple worksheets. I Have a Macro which prints all worksheets to pdf. I want to change the macro to only print specific worksheets

Code sofar:

Sub Print_All_pages()

Dim ws As Worksheet
For Each ws In Worksheets


ws.Select
nm = ws.Name

Dim Filepath As String

Filepath = Range("Destination").Value & "\Übersicht Bonus - " & Range("Month").Value & " " & Range("Year").Value & " - " & nm & ".pdf"
 
 
Range("A1:N35").ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=Filepath, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=True, _
    OpenAfterPublish:=False


Next ws

Worksheets("Main Sheet").Activate

End Sub

I hav tried the following which doesn't work

For Each ws in Worksheets

If ws.Name = "Textbausteine Mail" Or ws.Name = "Overzichten verzenden" Or ws.Name = "Übersicht_Januar" Then

'Do nothing

Else

nm = ws.Name

Dim Filepath As String

Filepath = Range("Destination").Value & "\Übersicht Bonus - " & Range("Month").Value & " " & Range("Year").Value & " - " & ws.Name & ".pdf"


Range("A1:N35").ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=Filepath, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=True, _
    OpenAfterPublish:=False


Next ws

Worksheets("Main Sheet").Activate

This however throws me the error compilation error Next without for

Here is the workbook setup:

Any help is greatly appreciated

CodePudding user response：

Export Range to PDF

A Quick Fix

Option Explicit

Sub ExportToPDF()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim FilePathLeft As String
    With wb.Worksheets("Main Sheet")
        FilePathLeft = .Range("Destination").Value & "\Übersicht Bonus - " _
                & .Range("Month").Value & " " & .Range("Year").Value & " - "
    End With
    
    Dim ws As Worksheet
    Dim FilePath As String
    
    For Each ws In wb.Worksheets
        Select Case ws.Name
        ' do NOT export:
        Case "Textbausteine Mail", "Overzichten verzenden", "Übersicht_Januar"
        ' do export:
        Case Else
            FilePath = FilePathLeft & ws.Name & ".pdf"
            ws.Range("A1:N35").ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=FilePath, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=True, _
                OpenAfterPublish:=False
        End Select
    Next ws
    
End Sub