Automating Print preview of excel reports using vba

'this is for setting the center header of print which will print as Active Employee List
ersheet.PageSetup.CenterHeader = "Active Employee List"
'this is for setting the right footer of print which will print as Page (number of current page) of Total Pages
ersheet.PageSetup.RightFooter = "Page &P of &N"

'this commented out section works best with portrait printing
'ersheet.PageSetup.Zoom = 60
'ersheet.PageSetup.FitToPagesWide = 1

'ersheet.PageSetup.FitToPagesTall = False

'this section works best for landscape printing
With ersheet.PageSetup
'for setting portrait or landscape
.Orientation = xlLandscape
'these next two line will fit all columns in one page
.FitToPagesWide = 1
.FitToPagesTall = 1
'this line is responsible for continuing one fixed row on several print pages ,it is similar as excel freeze pane
.PrintTitleRows = ersheet.Rows(1).Address
End With

ersheet.PrintPreview

Output

Source:

https://docs.softartisans.com/officewriterwindows/3.0.5/ExcelWriterASP/features/headersandfooters.aspx

https://stackoverflow.com/questions/34052790/vba-code-to-set-print-area-fit-to-1x1-page-and-not-set-print-area-for-certain-t

https://www.youtube.com/watch?v=X4QBS94iNdo&list=PLw8O1w0Hv2zvnLFyiMrihcaOqA0sT0X2U&index=13