Followup to sheetname object questions

[owlnevada]

Please see original discussion for whole content
http://www.microsoft.com/communitie...a461&mid=0e486dcd-c64b-4861-b790-896ad4f9fae9

I'm looking at this code and it seems that it will work with some slight
modifications for what I'm trying to do. I want to select a subset of
worksheets within a workbook that all have the same value, a string in cell
N2 so that I can hardcode the "page # of #pages" similar to the footer
feature
meantioned here. The page numbering need to be inserted in cell N3. Often
we'll insert, say a blank page 9 to the previous 8 pages and want to avoid
having to renumber them manually to change page 7 of 8, and 8 of 8 to pages 7
of 9, 8 of 9, etc. . . I can select the first sheet to be renumbered
manually making it the activesheet and tell it to start there renumbering all
the sheets that have the same value in cell N2. i.e. 1 of 9, . . . 9 of 9.

Optionally, it would be more automatic to renumber the whole set of
worksheets contained in a large workbook of 150+ sheets when adding
additional items that require a new sheet be inserted to continue. The
numbering is all starting with 1 0f 1 to 1 to N# pages. Each set of sheets
has the same value in either cell N2 or C5 but likely has a different number
of sheets that need the page numbering. The sheets are always numbered from
left to right by index number sequence.

Thanks for any help in advance.

 

[paul.robinson]

Hi
bit complicated to create code that runs when sheets are added or
deleted. You could run this from a button or toolbar menu each time
you add some sheets

Sub tester()
Dim i As Long, SheetCount As Long, Pagecount As Long
Dim SheetCollection As New Collection
Dim ws As Variant
SheetCount = ActiveWorkbook.Sheets.Count
mytext = InputBox("What is the text in N2?")
For i = 1 To SheetCount
If ActiveWorkbook.Sheets(i).Range("N2").Value = mytext Then
SheetCollection.Add i
End If
Next i
Pagecount = SheetCollection.Count
i = 1
For Each ws In SheetCollection
ActiveWorkbook.Sheets(ws).Range("N3").Value = "Page " & i & " of "
& Pagecount & " Pages"
i = i + 1
Next ws
End Sub

regards
Paul

 

[owlnevada]

Such simplicity amazes me. Thats amost exactly what I was looking for!

I was intending to run this as a standalone macro after I had done my
inserting of new sheets, so your code works beautifully. Thanks a trillion.

You have made my day!