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 numbers 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 renumer 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.

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.

"Dave Peterson" wrote:

I think you'll have to find out the total number of sheets that would be printed
if you printed the whole workbook--then change the footer. Just hardcode that
page number into the footer.

And since one worksheet always has to be selected, does that mean that if only
one sheet is selected (or grouped), then that sheet should be included in the
pages to be printed? I'm gonna guess yes.

Option Explicit
Sub testme()
Dim wCtr As Long
Dim ArrNames() As String
Dim iCtr As Long
Dim myAddr As String
Dim wks As Worksheet
Dim mySelectedSheets As Sheets
Dim AddNameToArray As Boolean
Dim TotalPages As Long
Dim sh As Object

'get the total pages, sheet by sheet
TotalPages = 0
For Each sh In Sheets
TotalPages = TotalPages + ExecuteExcel4Macro("GET.DOCUMENT(50)")
Next sh

myAddr = "A10"

Set mySelectedSheets = ActiveWindow.SelectedSheets

ReDim ArrNames(1 To Worksheets.Count)
iCtr = 0
For wCtr = 1 To Worksheets.Count
AddNameToArray = False
With Worksheets(wCtr)
For Each wks In mySelectedSheets
If wks.Name = .Name Then
'in the grouped sheets, add it to the array
AddNameToArray = True
Exit For
End If
Next wks

If AddNameToArray = False Then
'look for that value
With .Range(myAddr)
If IsNumeric(.Value) Then
If .Value = 0 Then
'add it to the array
AddNameToArray = True
End If
End If
End With
End If

If AddNameToArray = True Then
iCtr = iCtr + 1
ArrNames(iCtr) = .Name
End If
End With
Next wCtr

If iCtr 0 Then
'found at least one
'resize the array
ReDim Preserve ArrNames(1 To iCtr)
For wCtr = LBound(ArrNames) To UBound(ArrNames)
Worksheets(wCtr).PageSetup.CenterHeader _
= "Page &P of " & Format(TotalPages, "#,##0")
Next wCtr
Worksheets(ArrNames).PrintOut preview:=True
End If

End Sub

driller wrote:

Hi Dave,
Here is a sample scenario of what i we are up to...

sheetname *VALUE of A10*
MASTER
sheet1-1 10
sheet1-2 0
sheet1-3 11
sheet1-4 0
sheet1-5 3
sheet1-6 0
sheet1-7 4
sheet1-8 0
sheet1-9 2

THEN *I group* sheet1-1 down to sheet1-9 and print preview with *footer*
will look like this

sheetname Footer : *&[PAGE] of &[PAGES]*
sheet1-1 1 of 9
sheet1-2 2 of 9
sheet1-3 3 of 9
sheet1-4 4 of 9
sheet1-5 5 of 9
sheet1-6 6 of 9
sheet1-7 7 of 9
sheet1-8 8 of 9
sheet1-9 9 of 9

then I run the *MACRO* to select the sheets with A10=0, then I press print
sheetname Footer : *&[PAGE] of &[PAGES]* *VALUE of A10*
sheet1-1 *do not print* 10
sheet1-2 2 of 9 0
sheet1-3 *do not print* 11
sheet1-4 4 of 9 0
sheet1-5 *do not print* 3
sheet1-6 6 of 9 0
sheet1-7 *do not print* 4
sheet1-8 8 of 9 0
sheet1-9 *do not print* 2

Hence I can print only the above 4 pages€¦{2,4,6,8}

regards,
driller
--
*****
birds of the same feather flock together..

"driller" wrote:

thanks for concerned reply,

it works well in grouping *ALL* the sheets of the workbook based on criteria
A10=0
1) is it possible that i first select *SOME* of the adjacent sheets that
shall be grouped and then run the macro..then the macro will refine the
selection of my Group with A10=0.
2) i need to preserve the sheet numbering from left to right in order to
trace which sheettabs has A10<0..meaning after running the macro..and i
print preview [or print to file] the refined group, then i can see my RIGHT
SIDE footer*&[Page] OF &[Pages]* in accordance to the real sequence of *ALL*
the sheets that i selected before running the macro..

If i have 10 sheets, and select only the next 9 contiguous sheets for the
group.
so if the 9 sheets have A10=0, the &[PAGES] will be 9.
and also
If i have 10 sheets, and select only the next 9 contiguous sheets for the
group.
and if there are 5 non-adjacent sheets with A10<0, the &[PAGES] will also
be 9 and the &[PAGE] field will be based on the arrangement of the 9 sheets...
I am also not allowed to move the position of sheet tabs in this workbook.
i believe its possible with your help to complete this.

thanks and regards,
driller
--
*****
birds of the same feather flock together..



"Dave Peterson" wrote:

Maybe something like:

Option Explicit
Sub testme()
Dim wCtr As Long
Dim ArrNames() As String
Dim iCtr As Long
Dim myAddr As String

myAddr = "A10"

ReDim ArrNames(1 To Worksheets.Count)
iCtr = 0
For wCtr = 1 To Worksheets.Count
With Worksheets(wCtr)
With .Range(myAddr)
If IsNumeric(.Value) Then
If .Value = 0 Then
'add it
iCtr = iCtr + 1
ArrNames(iCtr) = .Parent.Name
End If
End If
End With
End With
Next wCtr

If iCtr 0 Then
'found at least one
'resize the array
ReDim Preserve ArrNames(1 To iCtr)
Worksheets(ArrNames).PrintOut preview:=True
End If

End Sub



driller wrote:

hello again,

i have posted these before in other forum..i forget that this may be a
little complicated in this forum...

in myWorkbook i have many sheets,
the sheet names has no typical name pattern

is it possible to print the group of sheets in one command under a typical
criteria.
that is, *print the sheet if cell A1=0*, yet the footer page numbers must be
maintained.

If the there are 30 sheets in the grouptab, while 10 sheets contains *A10*,
then only sheets will be printed.

The footer paging SEQUENCE numbers shall correspond to the arrangement of
the sheets (L to R) in the workbook regardless of the above criteria....

hope its simple.<saving trees!

regards,
driller
--
*****
birds of the same feather flock together..

--

Dave Peterson


--

Dave Peterson