Hi ,

Try to use this one in "ThisWorkbook" Module :
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Range("A1").Value = "Sheet " & ActiveSheet.Index & " of " & Sheets.Count
End Sub

Rgds,
Halim


"jnf40" wrote:

I have a workbook that adds worksheets, names them and sorts them...My sheet
names are fine as they are with the cell entry...On the worksheet itself I
have a cell with 'Sheet' typed in it then a blank cell named Sht_of_ , the
next cell has 'of' typed in it then a blank cell named Sht_of_1...Looks
something like this,
Sheet_____ of _____...I want the numbering to go into these cells named
Sht_of_ and Sht_of_1...So if I have 2 worksheets named DBL ARROW and
DBL ARROW (2)...then
worksheet DBL ARROW would have Sheet 1 of 2 and
worksheet DBL ARROW (2) would have Sheet 2 of 2
if another worksheet was created later and it's name was
DBL ARROW (3) then
sheet DBL ARROW cells would change to Sheet 1 of 3
sheet DBL ARROW (2) cells would change to Sheet 2 of 3...and
sheet DBL ARROW (3) cells would be Sheet 3 of 3.