Log In
Register
Home
Active Member
September 10, 2019
Offline
Hi. I have various workbooks that I want to be able to open, and then save each individual worksheet as a new PDF using the tab name as the file name. I have this working fine using the below code however, I've now found out that some of the workbooks I'll be applying this to have hidden sheets and when I run it on these, I get Run-Time error '1004' Method 'Select' of object '_Worksheet' failed.
Would someone be able to point me in the right direction of how to overcome this. The workbooks I am applying this to can have any number of hidden sheets, the sheets will have different names and there is no set location of them within the workbook i.e. there's not two hidden ones at the beginning so I could just start this from tab 3.
Many thanks in advance for you assistance with this.
Sub ExportToPDFs()
' PDF Export Macro test
' Change H:\Q1 2019\ to your folder path where you need the files saved
' Save Each Worksheet to a separate PDF file.
Dim ws As Worksheet
For Each ws In Worksheets
ws.Select
nm = ws.Name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="H:\Q1 2019\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Next ws
End Sub
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Laura,
Do you want to export visible sheets to pdf, or just the visible ones?
Use:
Dim SheetVisibility as long 'possible values: -1: xlsheetvisible, 0: xlsheethidden, 2: xlsheetveryhidden
For Each ws In Worksheets
SheetVisibility=ws.Visible
'to print only visible:
If sheetVisibility=-1 then
ws.Select
nm = ws.Name
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="H:\Q1 2019\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Next ws
If you want to export all sheets, you have to set them visible first, or refer to the actual sheet, not to ActiveSheet:
Dim SheetVisibility as long 'possible values: -1: xlsheetvisible, 0: xlsheethidden, 2: xlsheetveryhidden
For Each ws In Worksheets
SheetVisibility=ws.Visible
'to print only visible:
ws.visible=XlSheetVisible
nm = ws.Name
' ws.Select ' no longer needed, we refer below to ws instead of ActiveSheet
ws.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="H:\Q1 2019\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
ws.visible=SheetVisibility 'set it back as it was before
Next ws
Answers Post
1 Guest(s)
