Nov 25 2022 08:47 AM
Hi all,
Im looking for an excel VBA code to print each sheet individually and rename the pdf based on sheet name.
Any help would be greatly appreciated.
Thanks
Nov 25 2022 11:29 AM
Hi Jonathan,
Please have a look at this code.
It iterates through each sheet, prints it and saves it by its name in pdf format.
Let me know if it works for you.
Option Explicit
Sub udsPrintSheetsAndSaveAsPdf()
Dim shtThisSheet As Worksheet
Dim booRC As Boolean
Application.ScreenUpdating = False
booRC = True
For Each shtThisSheet In ActiveWorkbook.Worksheets
Sheets(shtThisSheet.Name).Select
If booRC Then booRC = udfPrintSheet()
If booRC Then booRC = udfSaveAsPdf(shtThisSheet.Name)
Next shtThisSheet
Application.ScreenUpdating = True
End Sub
Function udfPrintSheet() As Boolean
Application.ScreenUpdating = False
With ActiveSheet.PageSetup
.PrintArea = ""
.PrintTitleRows = ""
.PrintTitleColumns = ""
.Orientation = xlPortrait
.PaperSize = xlPaperA4 ' I assume you want to print on A4, if not...
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Application.PrintCommunication = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
Application.PrintCommunication = False
udfPrintSheet = True
End Function
Function udfSaveAsPdf(strSheetName As String) As Boolean
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ChDir "C:\temp" ' I assume you have a C:\temp folder...
ActiveSheet.ExportAsFixedFormat _
Type:=xlTypePDF, Filename:="C:\temp\" & strSheetName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
Application.DisplayAlerts = True
udfSaveAsPdf = True
End Function
Nov 28 2022 06:35 AM
Thank you for this, but im getting an error for this section:
Sheets(shtThisSheet.Name).Select
Error: "Select method of worksheet class failed"
What should i put to fix it?
Thanks,