parsing worksheet names

    I have created an Excel form (Engineering Change Notice) that has a Sheet1 and then a multisht that is copied as Sheet2, Sheet3, etc. from a command button on Sheet1. What I would like to do is fill in the "Page _ of _" cells on the various sheets. In otherwords, if I have three sheets I would like to automatically fill in Page 1 of 3, Page 2 of 3, Page 3 of 3, and so on. Is this possible to do?


    Thank you for any help you can provide.
    Doug

    Here goes the code

    Code
    Sub page_of()
Dim sht As Worksheet
For Each sht In Sheets
    sht.Range("A20").Value = "Page " & sht.Index & " of " & Sheets.Count
Next
End Sub


    If you want this for printing purpose... i will suggest another way to do this.
    On each sheet setup appropriate header or footer (Page # of #). then select all the sheets you want to print and see print priview. You will see that appropriate page numbers are set and works fine even if one sheet has more than one pages.


    Hope this Helps

    Thanks: ~Yogendra

    Thank you for your reply Yogendra. The code you supplied works, but it counts sheets that I don't want counted. I would like to count only the actual Sheets and not include ErrorLog, multisht, & Lists in the sheet count. (multisht and Lists are hidden worksheets. multisht is the master for Sheet2, etc. and Lists is the sheet for all of the drop down list on the form.) Is there a way to exclude these sheets when doing the count?


    Thank you for your help!
    Doug

    Try

    Code
    Sub page_of()
    Dim sht As Worksheet, i As Long
    i = 0
    For Each sht In Sheets
        If sht.Visible And sht.Name <> "multisht" Then
            i = i + 1
            sht.Range("A20").Value = "Page " & i & " of " & Sheets.Count - 3
        End If
    Next sht
End Sub

    Thank you so much Derk!! This code works just the way I wanted it to. I'm not much into programming. I know some basics and can follow some code. I know what I want the end result to be just not how to get there, very often.


    Thank you,
    Doug

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login