Print all (except one or two)
 

I have to admit, it isn't a combination I have played around with.

But, if you are going to run the code to do the sheets, why not include the
code to do the formatting before grouping the sheets.

Sub SelectSheets()
Dim bReplace As Boolean
Dim mySheet As Object
Dim wkSht as Worksheet
For Each wkSht In ThisWorkbook.Worksheets
If wkSht.Name = "Cover" Then
wkSht.PageSetup.LeftHeader = ""
Else
wkSht.PageSetup.LeftHeader = _
"&10Project: " & Sheets("notes").Range("project") & Chr(10) & _
"Cost Report: " & Sheets("notes").Range("reportno") & Chr(10) & _
"Date: " & Sheets("notes").Range("date").Value
End If
Next wkSht


bReplace = True
For Each mySheet In Sheets
If mySheet.Name < "Notes" Then
With mySheet
If .Visible = True Then
.Select Replace:=bReplace
bReplace = False
End If
End With
End If
Next mySheet
ActiveWindow.SelectedSheets.PrintPreview
Worksheets("Notes").Select
End Sub

I would remove the BeforePrint code unless you need it. If you do, then you
might check to see if only a single sheet is being printed:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
If ActiveWindow.SelectedSheets.count 1 then exit sub
set wkSht = ActiveSheet
If wkSht.Name = "Cover" Then
wkSht.PageSetup.LeftHeader = ""
Else
wkSht.PageSetup.LeftHeader = _
"&10Project: " & Sheets("notes").Range("project") & Chr(10) & _
"Cost Report: " & Sheets("notes").Range("reportno") & Chr(10) & _
"Date: " & Sheets("notes").Range("date").Value
End If

End sub

--
Regards,
Tom Ogilvy


"Saintsman" wrote in message
...
Tom
Solve one part of a puzzle which stops another
The code woks fine
I have a beforeprint to assign page headers to the report

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In ThisWorkbook.Worksheets
If wkSht.Name = "Cover" Then
wkSht.PageSetup.LeftHeader = ""
Else
wkSht.PageSetup.LeftHeader = _
"&10Project: " & Sheets("notes").Range("project") & Chr(10) & _
"Cost Report: " & Sheets("notes").Range("reportno") & Chr(10) & _
"Date: " & Sheets("notes").Range("date").Value
End If
Next wkSht
Dim bReplace As Boolean
End Sub

When I manually select sheets & print preview the pages header updates -
when I run the VBA below it doesn't.
I have tried recording a simple select all & preview macro & then run
that,
but even that doesn't update my header
Is there a general issue with macros & beforeprint events which somehow
cancel each other out

Saintsman


"Tom Ogilvy" wrote:

This should handle that:

Sub SelectSheets()
Dim bReplace As Boolean
Dim mySheet As Object
bReplace = True
For Each mySheet In Sheets
If mySheet.Name < "Notes" Then
With mySheet
If .Visible = True Then
.Select Replace:=bReplace
bReplace = False
End If
End With
End If
Next mySheet
ActiveWindow.SelectedSheets.PrintPreview
Worksheets("Notes").Select
End Sub

--
Regards,
Tom Ogilvy


"Saintsman" wrote:

Tom
Can't make this work - what is the logic?
The message box pops up, but when I select sheet I need to run the
macro
again. I want to have a button on my notes page which I can click &
report
prints

Thanks for the efforts

Saintsman

"Tom Ogilvy" wrote:

Sub SelectSheets()
Dim mySheet As Object
If activesheet.Name = "Notes" then
Msgbox "Select first sheet to be printed
exit sub
end if
For Each mySheet In Sheets
if mySheet.Name < "Notes" then
With mySheet
If .Visible = True Then .Select Replace:=False
End With
end if
Next mySheet
ActiveWindow.SelectedSheets.PrintPreview

End Sub

--
Regards,
Tom Ogilvy

"Saintsman" wrote:

Both of these 'nearly work!
I need to group the pages for page numbering, these routines print
one sheet
at a time. I used the following to print all sheets, can I insert a
"not if
sheet="Notes" somewhere in this

Sub SelectSheets()
Dim mySheet As Object
For Each mySheet In Sheets
With mySheet
If .Visible = True Then .Select Replace:=False
End With
Next mySheet
ActiveWindow.SelectedSheets.PrintPreview

End Sub

"Tom Ogilvy" wrote:

Dim sh as worksheet
for each sh in thisworkbook.worksheets
if sh.Name < "Notes" then
sh.printout
end if
Next

--
Regards,
Tom Ogilvy


"Saintsman" wrote:

How do I print all sheets in a workbook, except for a couple at
the front end
I have a workbook with a notes sheet which does not need
including, but
additional sheets can be added by user & need to be included in
final report

Any ideas please

Print all (except one or two)
 

First of all, this thread is great because it answered a question that's been
troubling me. So thank you very much. The following is more of a rant,
sorry...

Does a comprehensive reference exist anywhere that would have explained the
fact that is implied in Tom's last response that workbook_beforeprint() works
exactly as one would expect it to *UNLESS* one selects multiple worksheets
and then expects all selected sheets to be printed after the code runs? I
mean why would anyone reasonably expect/predict ex ante this beforeprint code
would behave differently depending on whether one worksheet or multiple
worksheets are selected?

This discussion group is great. I've learnt a lot from the questions,
answers & references in the short time I've spent here. However it would be
really nice if a comprehensive reference is available so that a lot of
needless trial & error, such as that wherein I could not figure out why
beforeprint would not print multiple selected worksheets, can avoided.

End of rant. :)

"Saintsman" wrote:

Tom
Thanks v much for your help

"Tom Ogilvy" wrote:

I have to admit, it isn't a combination I have played around with.

But, if you are going to run the code to do the sheets, why not include the
code to do the formatting before grouping the sheets.

Sub SelectSheets()
Dim bReplace As Boolean
Dim mySheet As Object
Dim wkSht as Worksheet
For Each wkSht In ThisWorkbook.Worksheets
If wkSht.Name = "Cover" Then
wkSht.PageSetup.LeftHeader = ""
Else
wkSht.PageSetup.LeftHeader = _
"&10Project: " & Sheets("notes").Range("project") & Chr(10) & _
"Cost Report: " & Sheets("notes").Range("reportno") & Chr(10) & _
"Date: " & Sheets("notes").Range("date").Value
End If
Next wkSht


bReplace = True
For Each mySheet In Sheets
If mySheet.Name < "Notes" Then
With mySheet
If .Visible = True Then
.Select Replace:=bReplace
bReplace = False
End If
End With
End If
Next mySheet
ActiveWindow.SelectedSheets.PrintPreview
Worksheets("Notes").Select
End Sub

I would remove the BeforePrint code unless you need it. If you do, then you
might check to see if only a single sheet is being printed:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
If ActiveWindow.SelectedSheets.count 1 then exit sub
set wkSht = ActiveSheet
If wkSht.Name = "Cover" Then
wkSht.PageSetup.LeftHeader = ""
Else
wkSht.PageSetup.LeftHeader = _
"&10Project: " & Sheets("notes").Range("project") & Chr(10) & _
"Cost Report: " & Sheets("notes").Range("reportno") & Chr(10) & _
"Date: " & Sheets("notes").Range("date").Value
End If

End sub

--
Regards,
Tom Ogilvy


"Saintsman" wrote in message
...
Tom
Solve one part of a puzzle which stops another
The code woks fine
I have a beforeprint to assign page headers to the report

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In ThisWorkbook.Worksheets
If wkSht.Name = "Cover" Then
wkSht.PageSetup.LeftHeader = ""
Else
wkSht.PageSetup.LeftHeader = _
"&10Project: " & Sheets("notes").Range("project") & Chr(10) & _
"Cost Report: " & Sheets("notes").Range("reportno") & Chr(10) & _
"Date: " & Sheets("notes").Range("date").Value
End If
Next wkSht
Dim bReplace As Boolean
End Sub

When I manually select sheets & print preview the pages header updates -
when I run the VBA below it doesn't.
I have tried recording a simple select all & preview macro & then run
that,
but even that doesn't update my header
Is there a general issue with macros & beforeprint events which somehow
cancel each other out

Saintsman


"Tom Ogilvy" wrote:

This should handle that:

Sub SelectSheets()
Dim bReplace As Boolean
Dim mySheet As Object
bReplace = True
For Each mySheet In Sheets
If mySheet.Name < "Notes" Then
With mySheet
If .Visible = True Then
.Select Replace:=bReplace
bReplace = False
End If
End With
End If
Next mySheet
ActiveWindow.SelectedSheets.PrintPreview
Worksheets("Notes").Select
End Sub

--
Regards,
Tom Ogilvy


"Saintsman" wrote:

Tom
Can't make this work - what is the logic?
The message box pops up, but when I select sheet I need to run the
macro
again. I want to have a button on my notes page which I can click &
report
prints

Thanks for the efforts

Saintsman

"Tom Ogilvy" wrote:

Sub SelectSheets()
Dim mySheet As Object
If activesheet.Name = "Notes" then
Msgbox "Select first sheet to be printed
exit sub
end if
For Each mySheet In Sheets
if mySheet.Name < "Notes" then
With mySheet
If .Visible = True Then .Select Replace:=False
End With
end if
Next mySheet
ActiveWindow.SelectedSheets.PrintPreview

End Sub

--
Regards,
Tom Ogilvy

"Saintsman" wrote:

Both of these 'nearly work!
I need to group the pages for page numbering, these routines print
one sheet
at a time. I used the following to print all sheets, can I insert a
"not if
sheet="Notes" somewhere in this

Sub SelectSheets()
Dim mySheet As Object
For Each mySheet In Sheets
With mySheet
If .Visible = True Then .Select Replace:=False
End With
Next mySheet
ActiveWindow.SelectedSheets.PrintPreview

End Sub

"Tom Ogilvy" wrote:

Dim sh as worksheet
for each sh in thisworkbook.worksheets
if sh.Name < "Notes" then
sh.printout
end if
Next

--
Regards,
Tom Ogilvy


"Saintsman" wrote:

How do I print all sheets in a workbook, except for a couple at
the front end
I have a workbook with a notes sheet which does not need
including, but
additional sheets can be added by user & need to be included in
final report

Any ideas please