automated page numbers with VBA

[nancyo]

I have many workbooks which contain many worksheets. Some of the worksheets have multiple pages.

Currently, I have to manually enter all the page numbers in the header (i.e. Page 1 of 87). The total number of pages includes all the worksheets (and all of their pages).

I have looked through VBA help and tried a number of combinations of the formatting codes for headers, and none of them differentiates the different worksheets. By this, each worksheet starts over as page 1.

Any suggestions? The major reason I want to be able to do this is I want to be able to add / subtract worksheets and NOT have to manually changes all the headers.

 

[nancyo]

OK - it works by selecting all the sheets and placing the "&[Page]..." in the header. But, once you ungroup the sheets, it does not (as expected).

I really want this to be dynamic, with no one having to change anything, but the sheets cannot be selected together, as we may want to print out selected sheets. I'm also looking for some way to do this without tying it in to printing (the ideas about the macro with pagebreaks is way over my head).

If I set up the suggested macro, it appears to work on the same principle. Can I use this macro, and have it display the print dialog box to select only the desired sheets? (xldialog?). I will try playing around with the suggested macro by zzydhf.

 

[nancyo]

Tried the suggested (easy macro), and keeps crashing at Sheets. select ??????

Also, anyone know why sometimes when I go to print out the entire workbook, the sheet numbers get all jumbled up???

 

[zzydhf]

the sheets.select is not working for me either and I swear it did before. Will look into it.

 

[nancyo]

According to the help menu, it SHOULD work. I swear, some days some things work, and other days they don't - just to drive us all crazy!!

 

[zzydhf]

it does not work if there are hidden sheets.

 

[nancyo]

Well, that would explain it. I have at least 20 hidden worksheets. Thanks!

 

[keithkemble]

Folks,
Just to complicate life, I have a wbook that has umpteeen sheets but the number of visible sheets varies.

Selecting each sheet and previewing will yield the page x of y.

I have a routine that prints all visible sheets but prints page 1 of 1 on each page.

Can the select all visible ( ctrl + click ws) be automated within VBA and the page x of y be automated without previewing?

Thanks for any help

 

[cobdElug]

On 2002-06-23 15:06, keithkemble wrote:
Folks,
Just to complicate life, I have a wbook that has umpteeen sheets but the number of visible sheets varies.

Selecting each sheet and previewing will yield the page x of y.

I have a routine that prints all visible sheets but prints page 1 of 1 on each page.

Can the select all visible ( ctrl + click ws) be automated within VBA and the page x of y be automated without previewing?

Thanks for any help

Sub Select_Visible_WorkSheets()
Dim shts As Variant, ws As Worksheet, x%
If Worksheets.Count > 0 Then
ReDim shts(1 To Worksheets.Count)
For Each ws In Worksheets
If ws.Visible Then
x = x + 1
shts(x) = ws.Name
End If
Next ws
End If
ReDim Preserve shts(1 To x)
Worksheets(shts).Select
End Sub
This message was edited by cobdElug on 2002-06-23 15:34

 

[nancyo]

OK - the above macro does run OK, but all the sheets remain selected. Once a different tab is selected, the page numbers revert to considering each worksheet as a document.

I tried adding a save function, but it didn't help.

Does anyone have any suggestions? zzydhf??

 

[zzydhf]

I will keep thinking on it, but I am at a loss for now....