Trying to count what number a worksheet is in a workbook

[melfinance]

I need to count what number a worksheet is in the workbook. The first sheet should display 1 in cell a1. Sheet number 2 should display 2 in a1. Continued so if I insert a sheet the workbook is able to tell me the number of worksheets in front of it from sheet 1. Please help

 

[Tazguy37]

Put this code in your ThisWorkbook module:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.EnableEvents = False
    Sh.Range("A1").Value = Sheets(ActiveSheet.Name).Index
    Application.EnableEvents = True
End Sub

Press Alt-F11 to open the VBE.
Press Control-R to open the Project Explorer.
Click "Microsoft Excel Objects" for the file you're working on.
Put the code in the correct sheet or module.
Open the Code pane with F7.
Paste the above code in.
Press Alt-Q to close the VBE and return to Excel.

Hope that helps!

 

[melfinance]

Thank You so much. It worked great!!

 

[Tazguy37]

Glad to help. Just remember it's there in case you ever want to turn it off!