Page 1 of 2 12 LastLast
Results 1 to 10 of 20

automated page numbers with VBA

This is a discussion on automated page numbers with VBA within the Excel Questions forums, part of the Question Forums category; I have many workbooks which contain many worksheets. Some of the worksheets have multiple pages. Currently, I have to manually ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255

    Default

    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.

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,186

    Default

    Hi nancyo,

    This can be done in VBA, but is a bit involved, mainly because the Excel object model does not provide properties relating to page numbering, such as the total number of pages that a worksheet will print, etc. Thus your code has to figure out the number of pages each worksheet will print, and add them up to get the total number of pages for all worksheets. To count the pages you have to use the number of pagebreaks. I believe that the number of pages in a worksheet is the (number of horizontal page breaks + 1) x (number of vertical page breaks + 1).

    Once you have the total count, I believe your code will need to print each page, one at a time, putting the numbers in the headers individually. If you have a lot of other stuff in the header in addition to the page number such that finding where the page number should be written could be a problem, simply put a text string like

    of

    in the header. Then have your VBA code search and replace the string with the computed page number, and with the computed total page count.

    I hope this at least gets you started.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm
    (My other life: http://damonostrander.com )

  3. #3

    Join Date
    Feb 2002
    Posts
    47

    Default

    Try the following which should print all the sheets in the workbook with the required page numbers in A1.
    For each sheet, therefore, you need to include row 1 in "Rows to repeat at top" before running the macro.


    Sub PageCountHeader()
    Dim ws As Worksheet, totPages%, p%, x%
    For Each ws In Worksheets
    totPages = totPages + Application.ExecuteExcel4Macro("GET.DOCUMENT(50,""" & ActiveSheet.Name & """)")
    Next
    p = 1
    For Each ws In Worksheets
    ws.Activate
    For x = 1 To Application.ExecuteExcel4Macro("GET.DOCUMENT(50,""" & ActiveSheet.Name & """)")
    [A1].Value = "Page " & p & "of " & totPages
    ActiveSheet.PrintOut From:=x, To:=x
    p = p + 1
    Next x
    [A1].ClearContents
    Next ws
    End Sub


  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    N.E. Ohio
    Posts
    236

    Default

    If you just put the standard "&[Page] of &[Pages]" in the header and select all the sheets at once, it should work fine. It does for me.
    Oh, you hate your job? Why didn't you say so? There's a support group
    for that. It's called EVERYBODY, and they meet at the bar. - Drew Carey

  5. #5

    Join Date
    Feb 2002
    Posts
    47

    Default

    On 2002-06-08 22:09, zzydhf wrote:
    If you just put the standard "&[Page] of &[Pages]" in the header and select all the sheets at once, it should work fine. It does for me.

    That is not what nancyo is looking for.

    She wants the page numbers to be consecutive not by sheet but by workbook.

    That is, if Sheet1 has 5 pages, then the first page of Sheet2 would be page 6.

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    N.E. Ohio
    Posts
    236

    Default

    It does do that if you select all the sheets before printpreview or printing. Then if you just want the first sheet on page 2 (5 of 6) you would have to specify that in in the Print dialog box. I realize it is not automatic, but it works.

    *edit*
    Sub EditHeader()
    Dim sh As Worksheet
    For Each sh In Worksheets
    sh.PageSetup.CenterHeader = "&P of &N"
    Next

    Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
    'you will have to put your sheetnames here
    'or "sheets.select" for all sheets
    ActiveWindow.SelectedSheets.PrintPreview
    End Sub

    _________________
    Oh, you hate your job? Why didn't you say so? There's a support group
    for that. It's called EVERYBODY, and they meet at the bar. - Drew Carey

    [ This Message was edited by: zzydhf on 2002-06-09 10:10 ]

    [ This Message was edited by: zzydhf on 2002-06-09 10:16 ]

  7. #7

    Join Date
    Feb 2002
    Posts
    47

    Default

    You are quite right. I didn't know that.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255

    Default

    OK - I've read thru all your answers, and will give it a go this AM. Prefer a non-VBA approach, but will look at both. I will respond ASAP. THANKS!!!!!!!!!!!!!!!!!!!!!!!

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Massachusetts, USA
    Posts
    255

    Default

    zzydhf - if i want to use the "[Page... option you suggested, how do I "select all the sheets at once"?

  10. #10
    Vas
    Vas is offline
    Board Regular
    Join Date
    May 2002
    Location
    Gothenburg, Sweden
    Posts
    74

    Default

    1.Select the leftmost sheet.
    2.Hold Shift, press rightmost sheet.
    /Niklas Jansson

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com