Page Numbers

[eaddi]

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 out of the 160 contains 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


[VBA]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[/VBA]
5. Rerun all of the pages in the printer just to add page numbers

[Dave]

160 sheets? Yuck! Anyways, start by adding another sheet. Call it the output print sheet. Vba program to extract your data from the other 160 sheets as necessary and produce the output report you want. Make the output report the print size desired. Repeat clearing, filling and printing the desired output sheet as needed. Messing around with the print command settings will probably just make you mad. Good luck. Dave

[eaddi]

Dave,

Yea, someone thought a massive workbook was a great idea. How should I set up the print output sheet? What is the format? I remeber see one the other year, but it was a single line of code. I know I can't recreate that from just my memory alone.


Thx Ella

[snb]

I think Word has been designed for that:
You will have to adapt the filepath & name and the ranges for the pages.
[VBA]
Sub M_snb()
c00="G:\\OF\\adressen.xls"
with createobject("Word.document")
for j=1 to 5
.Fields.Add .Paragraphs.Last.Range, wdFieldEmpty, "INCLUDETEXT " & c00 & choose(j,"Sheet1!A1:K10","Sheet8!A1:K10","Sheet4!A1:K10","Sheet1!A1:K10","S heet45!A1:K10")
.Content.InsertAfter vbCrLf
.Paragraphs.Last.Range.InsertBreak
next
.fields.update
.printout 0
end with
End Sub
[/VBA]

[eaddi]

[VBA]Sub M_snb()
c00="Address my my excel file"
With createobject("Word.document")
For j=1 To 5 Will J create the page numbers?
.Fields.Add .Paragraphs.Last.Range, wdFieldEmpty, "INCLUDETEXT " & c00 & choose(j,"Sheet1!A1:K10","Sheet8!A1:K10","Sheet4!A1:K10","Sheet1!A1:K10","S heet45!A1:K10") VBA is requesting I debug this line. I modified it to just include Sheet1 and Sheet2 to test it.
.Content.InsertAfter vbCrLf
.Paragraphs.Last.Range.InsertBreak
Next
.fields.update
.printout 0
End With
End Sub[/VBA]

[eaddi]

Ok, here are some idea I am playing with. They don't work, but maybe this will help someone help me.

The first code is setup to loop though the ranges on one tab. The second is grouping other tabs and printing them.

What I need to happen is

Page 1 = Index tab
Page 2 = Range 4 on tab 40
Page 3 = Tab 24
Page 3 = Range 1 on tab 42
etc ..........

[VBA]
In this example I am playing with referencing another tab where I have numbered each page and range. The only problem is I can't group the ranges.

Sheets("Budget Variance").Activate
Dim Stark(100) As String
Dim Bark(100) As Long
Stark(20) = "IncomeStatementDet1"
Stark(21) = "IncomeStatementDet2"

'Printing Income Statements
Bark(10) = 20
Do Until Bark(10) = 22
Range(Stark(Bark(10))).Select
ActiveSheet.PageSetup.PrintArea = Stark(Bark(10))
With ActiveSheet.PageSetup
.LeftFooter = .Sheet("Hello").Range("b4")
.FitToPagesWide = 1
.FitToPagesTall = False
.Orientation = xlPortrait
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Bark(10) = Bark(10) + 1
Loop

End Sub[/VBA]

[snb]

Can you please use codetags ?