I'm trying to use .PageSetup to get a few pages in a workbook ready for print, but I'm having trouble getting the expected response with Title Rows, and I can't find a good code example for using VBA to insert a header (and footer) with cell references.
Below is the code that I have, and I need to add to it 1) Title Rows 1 and 2 to print at the top of every page, 2) a center page header that says "(the contents of Sheets("Main Data").Range(C4)) TLD Period (the contents of Sheets("Main Data").Range(C2))", and 3) a center page footer that says "(the current page number) of (total pages)".
I'm aware that I can set the first and third items easily in the page setup window (I don't know how to use cell references for the header), but I'm trying to make this file idiot proof for future users, so I would like VBA to have hidden control in case a user messes up the page setup properties available within the main file.
Below is the code that I have, and I need to add to it 1) Title Rows 1 and 2 to print at the top of every page, 2) a center page header that says "(the contents of Sheets("Main Data").Range(C4)) TLD Period (the contents of Sheets("Main Data").Range(C2))", and 3) a center page footer that says "(the current page number) of (total pages)".
I'm aware that I can set the first and third items easily in the page setup window (I don't know how to use cell references for the header), but I'm trying to make this file idiot proof for future users, so I would like VBA to have hidden control in case a user messes up the page setup properties available within the main file.
Code:
Lr = Sheets("Figure 2-2").Range("B" & Rows.Count).End(xlUp).Row
With Sheets("Figure 2-2")[INDENT].PageSetup.PrintArea = "A:F" & Lr
.PageSetup.Orientation = xlPortrait
.PageSetup.FitToPagesWide = 1
.PageSetup.FitToPagesTall = False
.PrintPreview[/INDENT]
End With
