Printing page numbers for every tabsheets in Excel

[santosh1]

Rate Member posted January 03, 2003 10:30 AM
--------------------------------------------------------------------------------
Hi-

I would like to print page numbers at the footer section for every tabsheets, which is about 80 tabsheets. I am using the following code, however sometimes, it doesn't print everything that is in the tabsheet. Is there a different way I can achieve this and how can I print everything on the tabsheet with the page numbers on the footer section? I will appreciate your help much.

Dim printarray As Variant
Dim i As Integer
Dim PrintSheetCount As Integer

' this is for the 7 tabs, you can add as many tabs

PrintSheetCount = 7
printarray = Array("GM OVERVIEW", _
"T ASSUMPTIONS", _
"T KEY PLUSMINUS", _
"UNFUNDED SPEND", _
"T PL SUMMARY", _
"B PL SUMMARY", _
"M PL SUMMARY&quot

For i = 0 To (PrintSheetCount - 1)
' Debug.Print Sheets(printarray(i)).Name
With Sheets(printarray(i)).PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = "&D &T"
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.25)
.FooterMargin = Application.InchesToPoints(0.25)
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Zoom = False
.RightFooter = "&F\&A"
.FitToPagesWide = 1
.FitToPagesTall = 1
.CenterFooter = "Page &P of &N"
End With
Next
Sheets(printarray).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

best regards,
Santosh

 

[RobBroekhuis]

Are you sure that the worksheet.printarea property for each of the sheets is set properly (i.e., to "" if you want to print all the information)?
Rob

 

[santosh1]

Thanks a lot for your help!!! I think this will work.

I have one more thing to fix, when I print all the tabsheets, for some tabsheets, everything fits in one page and the font is so small I hardly can read it. I saw the page setup for these tabsheets and saw the scaling that everything is set to fit in 1 page. I changed the setting Adjust to 55%, but when I print using the macros, it returns to the original setting to git in 1 page and prints in small font. How would I be able to fix this?
I appreciate your help much.

best regards

 

[RobBroekhuis]

In your code, remove the lines

.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1

and replace them with

.zoom=55

Rob