Page numbers- VBA Code needed

eaddi

New Member
Joined
Apr 2, 2012
Messages
24
I need help with adding page numbers to individual tabs and ranges in a single workbook.

Background:
One workbook
160 tabs in total
7 tabs with at least 5 report ranges each

The problem: Page 1 is on one tab, Page 2 may refer to a range on tab 8, Page 3 is the range on tab 4, and page 5 is tab 45.
Wanted Soultion: A way to actomattically print oahe numifprint the reportd

My brainstorming thoughts
1. Creating a page number tab and use it as a reference in my vba code
2. Manually assign page numbers in the print format, which will create havoc if a report is added or removed
3. I thought of using an input paramter box that would allow me to enter the page number I want to start with
4. Get help


Print Range Code
Sub PRTINC
Adding page numbers for the non ranged items, I could deal with. But the code for printing the ranges are Sheets("MT Financial Book Balance Sheet").Activate
Dim Stark(100) As String
Dim Bark(100) As Long
Stark(10) = "BalanceSh1"
Stark(11) = "BalanceSh2"
Stark(12) = "BalanceSh3"
Stark(13) = "BalanceSh4"

'Printing Balance Sheets
Bark(10) = 10
Do Until Bark(10) = 14
Range(Stark(Bark(10))).Select
ActiveSheet.PageSetup.PrintArea = Stark(Bark(10))
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = False
.Orientation = xlLandscape
End With
End sub


ActiveWindow.SelectedSheets.PrintOut Copies:=1
Bark(10) = Bark(10) + 1
Loop
5. Rerun all of the pages in the printer just to add page numbers
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,604
Members
412,330
Latest member
carlosjw
Top