Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sheet x of y (WIN XP/Access 97)

    I know I can use & Page &[Page] of &[Pages] in my footer to represent the pages of an individual worksheet.
    (Would appear as Page 1 of 3, for example)

    What I would like to have in my footer, (example has 5 worksheets, 1 page each) is:
    Sheet 1 of 5

    I experimented with & Sheet &[Sheet] of &[Sheets] which I know does not work.

    How can I produce this in my footer ?

    Thank you.

    PS - I probably need a tutorial in using the MSKB (and Woody's) Search function better.
    I did type in Sheet, footer, format and could not find this answer.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sheet x of y (WIN XP/Access 97)

    You have to use code to set this:

    Sub SetFooter()
    Dim w As Worksheet
    Dim i As Integer
    Dim n As Integer

    n = Worksheets.Count
    For i = 1 To n
    If Worksheets(i) Is ActiveSheet Then Exit For
    Next i

    Set w = ActiveSheet
    w.PageSetup.CenterFooter = "Sheet " & i & " of " & n
    End Sub

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet x of y (WIN XP/Access 97)

    I went to Alt F11 to get to the VB Editor.
    Insert Module
    Pasted the code

    Initially, it didn't work. Then I did a "Step Through". Went back to the sheet's footer, and it was
    there for Sheet1.
    Sheets 2-5 it did not appear. I then went into each sheet individually and pasted the code
    and had to "Step Through" each one to get it to work.

    Then I added a (6th) worksheet at the end and added the code - it's footer read Sheet 6 of 6 (correctly)
    but the first 5 sheets remained Sheet x of 5

    What do I need to modify to get this to work?

    Thanks again !
    Michael

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sheet x of y (WIN XP/Access 97)

    Try this:

    - Activate the Visual Basic Editor.
    - Double click ThisWorkbook.
    - Copy the following code into the ThisWorkbook module:

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim i As Integer
    Dim n As Integer

    n = Sheets.Count
    For i = 1 To n
    Sheets(i).PageSetup.CenterFooter = "Sheet " & i & " of " & n
    Next i
    End Sub

    This event procedure will run automatically each time you print or print preview, and will update the text in the footer.

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sheet x of y (WIN XP/Access 97)

    You are amazing Hans !

    Thank you VERY much. It works like a charm.

    Much, much appreciated !

    Michael

Posting Permissions

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