|
lookup for bright solution...
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.. |
lookup for bright solution...
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 |
lookup for bright solution...
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 |
lookup for bright solution...
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 |
lookup for bright solution...
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 |
lookup for bright solution...
Almost!!! You really help!!!
thanks Sir Dave maybe one last try, the macro indeed select the sheets Except the first selected sheet in the group where A10<0, the rest of the macro-selected sheets passed to the criteria of A10=0... What shall i do with the code so it will also remove the first sheet *in the group* when A10<0? One more try and i can *possibly* do the multiselected-page printing without hesitation... No problem with the &[Page] of &[Pages] to hardcode it in the page footer...maybe theres no macro to do the specific... i will just reserve a formulated cell to replace the footer function, and include this cell in the print page... thanks and regards, driller -- ***** birds of the same feather flock together.. "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 |
lookup for bright solution...
First, the code looped through all the pages to get the totalpages count. Then
it put it in the header for you. You'll have to change that portion of the code if you don't like it in the .centerheader. Untested--I just added more checks: 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. 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 If IsNumeric(wks.Range(myAddr)) Then If wks.Range(myAddr).Value = 0 Then 'in the grouped sheets, add it to the array AddNameToArray = True Exit For End If End If 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: Almost!!! You really help!!! thanks Sir Dave maybe one last try, the macro indeed select the sheets Except the first selected sheet in the group where A10<0, the rest of the macro-selected sheets passed to the criteria of A10=0... What shall i do with the code so it will also remove the first sheet *in the group* when A10<0? One more try and i can *possibly* do the multiselected-page printing without hesitation... No problem with the &[Page] of &[Pages] to hardcode it in the page footer...maybe theres no macro to do the specific... i will just reserve a formulated cell to replace the footer function, and include this cell in the print page... thanks and regards, driller -- ***** birds of the same feather flock together.. "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 -- Dave Peterson |
lookup for bright solution...
thanks Sir Dave,
I found the auto-header embedded yet the page number per page do not JUMP when the unselected sheets are eliminated in the group...is it possible to maintain the page number per page assuming that nothing had been unselected in the group.... if this is not possible...please inform me so i will quit asking for a maybe *impossible*.. I can hardcode the &[pages] before printing but the &[page] is my problem... Also, one thing, I tested testme() for a group of 62 sheets and the header only appears from page 1 to 56 of 62, the next page headers 57 to 60 of 62 do not appear in the print preview...dont know why? thanks and regards, driller -- ***** birds of the same feather flock together.. "Dave Peterson" wrote: First, the code looped through all the pages to get the totalpages count. Then it put it in the header for you. You'll have to change that portion of the code if you don't like it in the .centerheader. Untested--I just added more checks: 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. 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 If IsNumeric(wks.Range(myAddr)) Then If wks.Range(myAddr).Value = 0 Then 'in the grouped sheets, add it to the array AddNameToArray = True Exit For End If End If 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: Almost!!! You really help!!! thanks Sir Dave maybe one last try, the macro indeed select the sheets Except the first selected sheet in the group where A10<0, the rest of the macro-selected sheets passed to the criteria of A10=0... What shall i do with the code so it will also remove the first sheet *in the group* when A10<0? One more try and i can *possibly* do the multiselected-page printing without hesitation... No problem with the &[Page] of &[Pages] to hardcode it in the page footer...maybe theres no macro to do the specific... i will just reserve a formulated cell to replace the footer function, and include this cell in the print page... thanks and regards, driller -- ***** birds of the same feather flock together.. "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 |
lookup for bright solution...
Ahh. I see what you mean...
Can you print each worksheet separately? Depending on your printer (and network printer settings), you may get a page separator for each worksheet you print. 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 PagesPrintedBeforeThisSheet() As Long Dim sh As Object 'get the total pages. TotalPages = 0 ReDim PagesPrintedBeforeThisSheet(1 To Sheets.Count) For wCtr = 1 To Sheets.Count PagesPrintedBeforeThisSheet(wCtr) = TotalPages TotalPages = TotalPages + ExecuteExcel4Macro("GET.DOCUMENT(50)") Next wCtr 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 sh In mySelectedSheets If TypeName(sh) = "Worksheet" Then If sh.Name = .Name Then If IsNumeric(sh.Range(myAddr)) Then If sh.Range(myAddr).Value = 0 Then 'in the grouped sheets, add it to the array AddNameToArray = True Exit For End If End If End If End If Next sh 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) Sheets(wCtr).PageSetup.FirstPageNumber _ = PagesPrintedBeforeThisSheet(Sheets(ArrNames(wCtr)) .Index) + 1 Sheets(wCtr).PageSetup.CenterHeader _ = "Page &P of " & Format(TotalPages, "#,##0") Sheets(wCtr).PrintOut preview:=True Next wCtr End If End Sub driller wrote: thanks Sir Dave, I found the auto-header embedded yet the page number per page do not JUMP when the unselected sheets are eliminated in the group...is it possible to maintain the page number per page assuming that nothing had been unselected in the group.... if this is not possible...please inform me so i will quit asking for a maybe *impossible*.. I can hardcode the &[pages] before printing but the &[page] is my problem... Also, one thing, I tested testme() for a group of 62 sheets and the header only appears from page 1 to 56 of 62, the next page headers 57 to 60 of 62 do not appear in the print preview...dont know why? thanks and regards, driller -- ***** birds of the same feather flock together.. "Dave Peterson" wrote: First, the code looped through all the pages to get the totalpages count. Then it put it in the header for you. You'll have to change that portion of the code if you don't like it in the .centerheader. Untested--I just added more checks: 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. 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 If IsNumeric(wks.Range(myAddr)) Then If wks.Range(myAddr).Value = 0 Then 'in the grouped sheets, add it to the array AddNameToArray = True Exit For End If End If 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: Almost!!! You really help!!! thanks Sir Dave maybe one last try, the macro indeed select the sheets Except the first selected sheet in the group where A10<0, the rest of the macro-selected sheets passed to the criteria of A10=0... What shall i do with the code so it will also remove the first sheet *in the group* when A10<0? One more try and i can *possibly* do the multiselected-page printing without hesitation... No problem with the &[Page] of &[Pages] to hardcode it in the page footer...maybe theres no macro to do the specific... i will just reserve a formulated cell to replace the footer function, and include this cell in the print page... thanks and regards, driller -- ***** birds of the same feather flock together.. "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 -- Dave Peterson |
lookup for bright solution...
thanks sir dave,
the latest code always waits for me to click the *close* button of the print preview page for *all* pages, maybe i need to choose from all codes u kindly availed. After more test, i feel to like the other one with a page header, I guess u can modify one or two lines on the last part of the code something like this... = "Page &P of " & Format(TotalPages, "#,##0") I like that the "{value A2} of " & Format(TotalPages, "#,##0") I hope u can help me with this, i'm young for macro..... Below is the cde for your kind finality. -------------------------------------- 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. 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 If IsNumeric(wks.Range(myAddr)) Then If wks.Range(myAddr).Value = 0 Then 'in the grouped sheets, add it to the array AddNameToArray = True Exit For End If End If 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 ------------------------------------------------- with sincere regards, driller -- ***** birds of the same feather flock together.. "Dave Peterson" wrote: Ahh. I see what you mean... Can you print each worksheet separately? Depending on your printer (and network printer settings), you may get a page separator for each worksheet you print. 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 PagesPrintedBeforeThisSheet() As Long Dim sh As Object 'get the total pages. TotalPages = 0 ReDim PagesPrintedBeforeThisSheet(1 To Sheets.Count) For wCtr = 1 To Sheets.Count PagesPrintedBeforeThisSheet(wCtr) = TotalPages TotalPages = TotalPages + ExecuteExcel4Macro("GET.DOCUMENT(50)") Next wCtr 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 sh In mySelectedSheets If TypeName(sh) = "Worksheet" Then If sh.Name = .Name Then If IsNumeric(sh.Range(myAddr)) Then If sh.Range(myAddr).Value = 0 Then 'in the grouped sheets, add it to the array AddNameToArray = True Exit For End If End If End If End If Next sh 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) Sheets(wCtr).PageSetup.FirstPageNumber _ = PagesPrintedBeforeThisSheet(Sheets(ArrNames(wCtr)) .Index) + 1 Sheets(wCtr).PageSetup.CenterHeader _ = "Page &P of " & Format(TotalPages, "#,##0") Sheets(wCtr).PrintOut preview:=True Next wCtr End If End Sub driller wrote: thanks Sir Dave, I found the auto-header embedded yet the page number per page do not JUMP when the unselected sheets are eliminated in the group...is it possible to maintain the page number per page assuming that nothing had been unselected in the group.... if this is not possible...please inform me so i will quit asking for a maybe *impossible*.. I can hardcode the &[pages] before printing but the &[page] is my problem... Also, one thing, I tested testme() for a group of 62 sheets and the header only appears from page 1 to 56 of 62, the next page headers 57 to 60 of 62 do not appear in the print preview...dont know why? thanks and regards, driller -- ***** birds of the same feather flock together.. "Dave Peterson" wrote: First, the code looped through all the pages to get the totalpages count. Then it put it in the header for you. You'll have to change that portion of the code if you don't like it in the .centerheader. Untested--I just added more checks: 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. 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 If IsNumeric(wks.Range(myAddr)) Then If wks.Range(myAddr).Value = 0 Then 'in the grouped sheets, add it to the array AddNameToArray = True Exit For End If End If 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: Almost!!! You really help!!! thanks Sir Dave maybe one last try, the macro indeed select the sheets Except the first selected sheet in the group where A10<0, the rest of the macro-selected sheets passed to the criteria of A10=0... What shall i do with the code so it will also remove the first sheet *in the group* when A10<0? One more try and i can *possibly* do the multiselected-page printing without hesitation... No problem with the &[Page] of &[Pages] to hardcode it in the page footer...maybe theres no macro to do the specific... i will just reserve a formulated cell to replace the footer function, and include this cell in the print page... thanks and regards, driller -- ***** birds of the same feather flock together.. "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 |
lookup for bright solution...
The print preview was used for testing. I would think you'd actually go to
paper when you turned it on for use. For wCtr = LBound(ArrNames) To UBound(ArrNames) Worksheets(wCtr).PageSetup.CenterHeader _ = "Page " & Worksheets(wCtr).range("A2").value _ & " of " & Format(TotalPages, "#,##0") Next wCtr driller wrote: thanks sir dave, the latest code always waits for me to click the *close* button of the print preview page for *all* pages, maybe i need to choose from all codes u kindly availed. After more test, i feel to like the other one with a page header, I guess u can modify one or two lines on the last part of the code something like this... = "Page &P of " & Format(TotalPages, "#,##0") I like that the "{value A2} of " & Format(TotalPages, "#,##0") I hope u can help me with this, i'm young for macro..... Below is the cde for your kind finality. -------------------------------------- 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. 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 If IsNumeric(wks.Range(myAddr)) Then If wks.Range(myAddr).Value = 0 Then 'in the grouped sheets, add it to the array AddNameToArray = True Exit For End If End If 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 ------------------------------------------------- with sincere regards, driller -- ***** birds of the same feather flock together.. "Dave Peterson" wrote: Ahh. I see what you mean... Can you print each worksheet separately? Depending on your printer (and network printer settings), you may get a page separator for each worksheet you print. 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 PagesPrintedBeforeThisSheet() As Long Dim sh As Object 'get the total pages. TotalPages = 0 ReDim PagesPrintedBeforeThisSheet(1 To Sheets.Count) For wCtr = 1 To Sheets.Count PagesPrintedBeforeThisSheet(wCtr) = TotalPages TotalPages = TotalPages + ExecuteExcel4Macro("GET.DOCUMENT(50)") Next wCtr 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 sh In mySelectedSheets If TypeName(sh) = "Worksheet" Then If sh.Name = .Name Then If IsNumeric(sh.Range(myAddr)) Then If sh.Range(myAddr).Value = 0 Then 'in the grouped sheets, add it to the array AddNameToArray = True Exit For End If End If End If End If Next sh 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) Sheets(wCtr).PageSetup.FirstPageNumber _ = PagesPrintedBeforeThisSheet(Sheets(ArrNames(wCtr)) .Index) + 1 Sheets(wCtr).PageSetup.CenterHeader _ = "Page &P of " & Format(TotalPages, "#,##0") Sheets(wCtr).PrintOut preview:=True Next wCtr End If End Sub driller wrote: thanks Sir Dave, I found the auto-header embedded yet the page number per page do not JUMP when the unselected sheets are eliminated in the group...is it possible to maintain the page number per page assuming that nothing had been unselected in the group.... if this is not possible...please inform me so i will quit asking for a maybe *impossible*.. I can hardcode the &[pages] before printing but the &[page] is my problem... Also, one thing, I tested testme() for a group of 62 sheets and the header only appears from page 1 to 56 of 62, the next page headers 57 to 60 of 62 do not appear in the print preview...dont know why? thanks and regards, driller -- ***** birds of the same feather flock together.. "Dave Peterson" wrote: First, the code looped through all the pages to get the totalpages count. Then it put it in the header for you. You'll have to change that portion of the code if you don't like it in the .centerheader. Untested--I just added more checks: 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. 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 If IsNumeric(wks.Range(myAddr)) Then If wks.Range(myAddr).Value = 0 Then 'in the grouped sheets, add it to the array AddNameToArray = True Exit For End If End If 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: Almost!!! You really help!!! thanks Sir Dave maybe one last try, the macro indeed select the sheets Except the first selected sheet in the group where A10<0, the rest of the macro-selected sheets passed to the criteria of A10=0... What shall i do with the code so it will also remove the first sheet *in the group* when A10<0? One more try and i can *possibly* do the multiselected-page printing without hesitation... No problem with the &[Page] of &[Pages] to hardcode it in the page footer...maybe theres no macro to do the specific... i will just reserve a formulated cell to replace the footer function, and include this cell in the print page... thanks and regards, driller -- ***** birds of the same feather flock together.. "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 -- Dave Peterson |
lookup for bright solution...
Sir Dave Peterson,
Perfect guidance ! Hoping you receive this note with my sincere gratitude! Looking forward for more Profs and MVPs like u in this forum, with kind participation. regards, driller -- ***** birds of the same feather flock together.. wisely, it's always more worth of knowing the edges rather than focusing in one line ! To know the limits and remember the extents! |
lookup for bright solution...
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 |
| All times are GMT +1. The time now is 06:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com