CODEWITHSUNDEEP
vbaexcelpdf
Scott B.
Scott B.

Reputation: 33

Macro to Export Certain Excel Sheets to Separate PDF

I have a macro to export certain sheets in a workbook to separate PDF's (for reporting purposes). It works properly for me in one workbook, however, in a different workbook it is exporting ALL sheets. I can't figure out where I am going wrong. To make things easier, I italicized the spots where I would customize it for my purposes.

Sub ExportToPDFs()  
' PDF Export Macro  
' C:\ *location*  
' Sheets(Array("*selected sheets*")).Select  

Dim nm As String  
Dim ws As Worksheet  

For Each ws In Worksheets  
ws.Select  
nm = ws.Name  

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _  
Filename:="C:\*location*" & "*Report Title*" & nm & Range("D8").Value & ".pdf", _  
Quality:=xlQualityStandard, IncludeDocProperties:=True, _  
IgnorePrintAreas:=False, OpenAfterPublish:=True  

Next ws  

End Sub

Upvotes: 2

Views: 4703

Answers (2)

ASH
ASH

Reputation: 20302

Great question! I think this should do what you want...jsut modify the script to suit your needs...

Sub ExportToPDFs()

Dim ws As Worksheet

For Each ws In Worksheets
ws.Select
nm = ws.Name

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\your_path_here\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Next ws

End Sub

Upvotes: 0

Kyle
Kyle

Reputation: 2545

You need to fill the array sheets_to_select with the names of the sheets you want this to export. Otherwise, this will work for you.

Sub ExportToPDFs()  
' PDF Export Macro  
' C:\ *location*  
' Sheets(Array("*selected sheets*")).Select  

Dim nm As String  
Dim ws As Worksheet  
Dim i as Variant, sheets_to_select as Variant

sheets_to_select = Array("Sheet1","Sheet2","Sheet3")

For Each i in sheets_to_select  
   Thisworkbook.Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, _  
      Filename:="C:\*location*" & "*Report Title*" & i & Thisworkbook.Sheets("*The sheet you need the value of D8 from").Range("D8").Value & ".pdf", _  
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _  
      IgnorePrintAreas:=False, OpenAfterPublish:=True  

Next i

End Sub

Upvotes: 1

Related Questions