Saving all worksheets to seperate PDF files but excluding specific worksheets

FreAnd · Feb 8th 2022

[xpost][/xpost]

https://www.mrexcel.com/board/…files-vba-coding.1195462/

Hi all,

I am a bit new to VBA coding but have managed to create a macro that lets me save multiple worksheets to separate pdf's.
But, I don't want to include two specific sheets, for ex, sheet TEMPLATE and SOURCE DATA how can I exclude these two and print the rest?

This is my code:

Code

Sub LoopSheetsSaveAsPDF()


'Create variables
Dim ws As Worksheet


'Loop through all worksheets and save as individual PDF in same folder
'as the Excel file
For Each ws In ActiveWorkbook.Worksheets




ws.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"






Next


End Sub

Display More

I would be very grateful for any assistance

My best regards from a cold Sweden,
Fredrik

FreAnd · Feb 8th 2022

The closest I can get is with this IF not statment, Now I can exclude the Source data sheet but still not the Template sheet,

Code

Sub LoopSheetsSaveAsPDF()



'Create variables

Dim ws As Worksheet



'Loop through all worksheets and save as individual PDF in same folder

'as the Excel file

For Each ws In ActiveWorkbook.Worksheets



If Not ws.Name <> "Template" Or ws.Name <> "Source data" Then





ws.ExportAsFixedFormat Type:=xlTypePDF, _

Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"





Else

End If

Next



End Sub

Display More

**royUK** · Feb 9th 2022

Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

How to use code tags

Just highlight all of the code and press the <> in the post menu above button to add the code tags.

Thanks.

**royUK** · Feb 9th 2022

Try this

Code

Sub LoopSheetsSaveAsPDF()
    'Create variables
    Dim ws As Worksheet
    'Loop through all worksheets and save as individual PDF in same folder
    
    'as the Excel file
    
    For Each ws In ActiveWorkbook.Worksheets
        
        Select Case ws.Name
            Case "Template", "Source data"
                ''/// ignore these sheets
            Case Else
                ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "/" & ws.Name & ".pdf"
        End Select
    Next ws
    
End Sub

Display More

FreAnd · Feb 9th 2022

Hi Roy,

Thank you very much, now it works!

Went through the Forum Rules too fast ( as usually

Will take a closer look and make sure to wrap in Code Tags next time

Again, Thank you!

Best regards
Fredrik

VBA Padawan

**royUK** · Feb 10th 2022

Also, read all the Rules, specifically Cross Posting.

Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the URL from the address bar in your browser) to the cross-post. We are here to help so help us help you!

Read this to understand why we ask you to do this

https://www.excelguru.ca/content.php?184

Saving all worksheets to seperate PDF files but excluding specific worksheets

Participate now!

Share

Tags