How to save multiple excel sheets as separate pdf files in different names?

[RyanWi]

Hi,

I have 1 workbook with 12 excel sheets.
Each contains an invoice, and i need to export all sheets to different PDF files and under different names.

Eg.
Sheet 1 - > Invoice 01 ---> Invoice01.pdf
Sheet 2 - > Invoice 02 ---> Invoice02.pdf
Sheet 3 - > Invoice 03 ---> Invoice03.pdf
Sheet 4 - > Invoice 04 ---> Invoice04.pdf
.....etc

Thanks.

 

[ranman256]

paste code into a module, then run : ExportAllPdfs

Sub ExportAllPdfs()
Dim ws As Worksheet
Dim vDir, vFile, vName

vDir = Environ$("USERPROFILE") & "\Documents\"

For Each ws In Sheets
  ws.Activate

vName = replace(ws.Name, "Sheet","Invoice")
  vFile = vDir & vName & ".pdf"
 
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=vFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Next
Set ws = Nothing
End Sub

 

[RyanWi]

paste code into a module, then run : ExportAllPdfs

Sub ExportAllPdfs()
Dim ws As Worksheet
Dim vDir, vFile, vName

vDir = Environ$("USERPROFILE") & "\Documents\"

For Each ws In Sheets
  ws.Activate

vName = replace(ws.Name, "Sheet","Invoice")
  vFile = vDir & vName & ".pdf"
 
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=vFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Next
Set ws = Nothing
End Sub

Hi,

Super thanks!
It works.

Only thing i want to change the location to
E:\EXCEL\Data\MonthName2023\

 

[RyanWi]

No worries, Figured it out.