Need Help in Print to PDF

[Prashant1211]

Hello VBA experts, I am looking for help to create a VBA code using which I can print all sheets of my workbook except sheet with Name "HW_Itemlist".
every sheet should have separate PDF
Print area - I want to print whole used range (Used range from D1 to P till last used range).
Sheet size A4,
orientation Landscape,
Margins Narrow,
Header : Page nos on Right top.
also important is that All the columns should be on one page while printing.
PDF file name to be range G4+sheet name
Saving location on Desktop under folder "HW Checklist" (Folder to be created if not exist)
Can anyone help me with the code please.

Please let me know if more information is required. Many thanks

 

[ChrisFoster]

Sub PrintSheets()
Dim ws As Worksheet
Dim printArea As Range
Dim pdfName As String
Dim saveFolder As String

'Create "HW Checklist" folder if it doesn't exist
If Dir(Environ("USERPROFILE") & "\Desktop\HW Checklist", vbDirectory) = "" Then
MkDir (Environ("USERPROFILE") & "\Desktop\HW Checklist")
End If

'Loop through all worksheets except for "HW_Itemlist"
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "HW_Itemlist" Then
'Set print area to used range from D1 to P till last used range
Set printArea = ws.Range("D1:P" & ws.Cells.SpecialCells(xlLastCell).Row)

'Set PDF file name to G4+sheet name
pdfName = ws.Range("G4").Value & " - " & ws.Name

'Set saving location on Desktop under folder "HW Checklist"
saveFolder = Environ("USERPROFILE") & "\Desktop\HW Checklist\"

'Print sheet to PDF with specified settings
With ActiveSheet.PageSetup
.PrintArea = printArea
.PaperSize = xlPaperA4
.Orientation = xlLandscape
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.75)
.BottomMargin = Application.InchesToPoints(0.75)
.HeaderMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0.3)
.HeaderRight = "&P"
End With
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveFolder & pdfName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End If
Next ws
End Sub