Excel: "Page 1" footer on every page of 100+ page report 2

[as0125]

In one workbook, I have a macro that prints out multiple pages of a data at different times. Specifically, calculations are performed at different times on different ranges of the worksheet/workbook.

There is a footer set up to print out the page number. Currently, "Page 1 of 1" is printed out for every page of the report (the report totals about 100 pages). This becomes a sorting nightmare if the report gets out of order.

Is there any way that the page number can be modified so that it is consecutively increasing?

 

[Molby]

Yep, go into page set up, click on the Header/Footer tab, then under footer, select "Page 1 of ?".

 

[SkipVought]

Hi,

What we have here is a failure to communicate!

What do YOU mean by multiple pages of a data?

One SHEET might print on multiple pages. In this case, If you had &[Page] of &[Pages] you'd get what you want.

However, if you are printing several sheets, it depends HOW you do it in order to get the desired result.

1) Each SHEET must have &[Page] of &[Pages]

2) SELECT the SHEETS you want to print together

3) Print

VOLA!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[as0125]

Molby,

I have that footer already set up -- and it prints out "Page 1 of 1" for every single page of my report.

I really don't even need the total ("of ?") part. I can display just "Page #" -- but the page number is currently not increasing.

 

[Molby]

Have to agree with Skip here, not sure where all of your data is...

Have you tried selecting "print what" under the Print option, it allows you to print a single page, a selection, or the entire workbook...

 

[as0125]

Sorry for not being clear enough about this:

One macro selects options from filters on the sheet, so that only certain data is displayed. Calculations are performed on the displayed data. Then, for each filtered group, automatically prints out a report. Then, macro loops through each option on the filters.

Example:
Filter (drop-down box) contains values: All, New York, Los Angeles, Chicago.

Macro selects "All" from list; performs calculations; prints out report pertaining to "All". Then selects "New York" from list; performs calculations; prints out report pertaining to "New York"....etc.

On report page for "All", the footer is "Page 1". On the report page for "New York", the footer is also "Page 1". I would like the "New York" report to display "Page 2".

By the way, the macro prints out the report using VBA: "ActiveSheet.PrintOut Copies:=1

 

[SkipVought]

In your macro, before you print, set the Visible Property of all sheets NOT to print to xlSheetHidden.

Then do ActiveWorkbook.PrintOut Copies:=1

Then UNHIDE all sheets, setting the Visible property to xlSheetVisible



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[as0125]

I'm not sure what you mean by:
set the Visible Property of all sheets NOT to print to xlSheetHidden

Only my current active sheet (eg: sheet One) gets printed anyway - which is correct. I don't think I need to "Hide" the other sheets (sheet Two), because they aren't related to the print job. Besides, I need sheet Two in order to select each option on sheet A's prior to printing.

Here is a snippet of my VBA code:

For Each P In Range("List")
Sheets("One").Select
ActiveSheet.Calculate
ActiveSheet.PrintOut Copies:=1
Sheets("Two").Activate
Next P

Range "List" is located on sheet Two.

Hope this makes sense.

 

[as0125]

I'm not sure what you mean by:
set the Visible Property of all sheets NOT to print to xlSheetHidden

Only my current active sheet (eg: sheet One) gets printed anyway - which is correct. I don't think I need to "Hide" the other sheets (sheet Two), because they aren't related to the print job. Besides, I need sheet Two in order to select each option on sheet A's prior to printing.

Here is a snippet of my VBA code:

For Each Item In Range("List")
Sheets("One").Select
ActiveSheet.Calculate
ActiveSheet.PrintOut Copies:=1
Sheets("Two").Activate
Next Item

Range "List" is located on sheet Two. What I want is for every printed page for each "Item" to be numbered consecutively.

Hope this makes sense.

 

[SkipVought]

For Each P In Range("List")
        Sheets("One").Select
        ActiveSheet.Calculate
        ActiveSheet.PrintOut Copies:=1
        Sheets("Two").Activate
Next P

what does each P in Range("List") do? Not a thing in this example!

I only see 2 Sheets. Are you printing out EVERYTHING on both sheets or just printing Sheet One?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[as0125]

Skip,

Well, on sheet Two, "List" includes corresponding values to drop-down box on sheet "One". So P signifies the current value that is selected on that drop-down box.

I am printing out only sheet One -- which is always fits a single page only.

 

[SkipVought]

So you're printing out the SAME PAGE 100+ times?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[as0125]

Technically, yes -- it is the same page. However, it is different data on every page because of the filter (drop-down box). Using the filter, the macro selects different items from the drop-down list. Then calculations are done based on the filter. Then the report is printed. Then the macro moves on to the next item on the drop-down list.

 

[SkipVought]

You then have 100+ Print Jobs in the Print Queue.

The ONLY way to accomplish anything close to what you want is to take &[Page] of &[Pages] out of Page Setup and instead, do the numbering in cells at the top or bottom of your sheet.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[as0125]

Okay. I'll have to try that out since the automatic numbering in the footer isn't doing what I want.

I'm wondering if there is a way to initiate some sort of counter variable (perhaps in VBA) to keep track of how many printing loops it has gone through. I guess I should post that as a separate thread.

Thanks for all your help (and patience) on this one though!

 

[SkipVought]

n = 1
[TotPages] = Range("List").count
For Each P In Range("List")
        Sheets("One").Select
 [PageNbr] = n
 n = n + 1
        ActiveSheet.Calculate
        ActiveSheet.PrintOut Copies:=1
        Sheets("Two").Activate
Next P

Name one cell TotPages (lets say D50) and the other PageNbr (lets say B50)
[tt]
A50: Page
B50:
C50: of
D50:
[/tt]



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[as0125]

Skip,

Thanks so much for your help! This is what I ended up with:

One cell named: PageNumber

Dim s AS String
n=1
t=Range("List").Count
For Each P In Range("List")
s="Page " & n & " of " &t
Range("PageNumber").Value=s
ActiveSheet.Calculate
ActiveSheet.PrintOut Copies=1
n=n+1
Next P

 

[as0125]

Update for anyone browsing through for similar info:

User can write footer information directly from VBA, without having to define a named range:

Dim s AS String
n = 1
t = Range("List").Count
For Each ListItem In Range("List")
s = "Page " & n & " of " & t
ActiveSheet.PageSetup.CenterFooter = s
ActiveSheet.Calculate
ActiveSheet.PrintOut Copies:=1
n = n + 1
Next ListItem