Code: Select all
Sub Export2PDF()
Dim strPath As String
Dim wsh As Worksheet
Application.Cursor = xlWait
' Get the path of the current workbook
strPath = ActiveWorkbook.Path & Application.PathSeparator
' Loop through the sheets
For Each wsh In Worksheets
Select Case wsh.Name
Case "Pivot1", "Pivot2", "List1", "List2"
' Skip these sheets; edit as needed
Case Else
' Show progress in status bar
Application.StatusBar = "Exporting " & wsh.Name
' Export A1:E54 to PDF
wsh.Range("A1:E54").ExportAsFixedFormat _
Type:=xlTypePDF, Filename:=strPath & wsh.Name & ".pdf"
End Select
Next wsh
' Reset the status bar
Application.StatusBar = False
Application.Cursor = xlDefault
End Sub