vba set zoom tile rows footer in Excel
Q176. In Excel, how can I set Page orientation, Zoom % , Title Rows and footer using VBA?
To do it in Excel, here is the answer:
- Option Explicit
- Sub PrintSetUp()
- ActiveSheet.PageSetup.PrintArea = "AP2:AR14"
- With ActiveSheet.PageSetup
- .PrintTitleRows = "$2:$2"
- .PrintTitleColumns = ""
- .CenterFooter = "Page &P of &N"
- .CenterHorizontally = True
- .CenterVertically = False
- .Orientation = xlLandscape
- .PaperSize = xlPaperA4
- .Zoom = 100
- End With
- End Sub
a) Line 5 - The rows specified (in the code above it is row 2) in Line 5 would appear in every page that is printed. In this example, since there is only one Page for Print, this command does not make a difference.
b) Line 6 - The columns specified (in the code above no column is specified as the print range does not span 2 pages) in Line 6 would appear in every page that is printed.
c) Line 7 - "&P" is the Page No and "&N" is the total number of pages. When the above code is executed, footer appears as "Page 1 of 1".
d) Line 10 - If Portrait printing is required, the field can be changed to xlPortrait instead of xlLandscape.