After serious thinking Vacuum Sealed wrote :
Whilst I have your attention Garry
You may be able to shed some light into why it is that the
With ActiveSheet.PageSetup does not work properly.
I have 4 report and each of them is almost identical say for a couple of
minor differences.
This is the 1st:
With ActiveSheet.PageSetup
.PrintArea = Selection.Address
.PrintTitleRows = "$1:$1"
.LeftHeader = "HDC"
.RightHeader = myDate
.FitToPagesWide = 1
End With
Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
ActiveWindow.SelectedSheets.PrintPreview
With ActiveSheet.PageSetup
.CenterHeader = ""
.PrintArea = ""
End With
This is the second:
With ActiveSheet.PageSetup
.PrintArea = Selection.Address
.PrintTitleRows = "$1:$1"
.LeftHeader = "NDC"
.RightHeader = myDate
.FitToPagesWide = 1
End With
Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
ActiveWindow.SelectedSheets.PrintPreview
With ActiveSheet.PageSetup
.CenterHeader = ""
.PrintArea = ""
End With
Problem is, when I run the 2nd report the custom header does not change it's
value, it still shows the 1st report header.
Any thoughts to why this is happening please....
TIA
Mick
Since the code refers to 'ActiveSheet' then it's being applied to that
sheet only. You might be better off making the code generic so you can
pass a ref to the target sheet, AND a parameter (arg) for LeftHeader.
If this code you posted is in a single procedure then you'd have to be
activating ach sheet in turn before each part of the code executes.
Not sure why you force PrintPreview before all sheets are setup. I
suspect it's so you can do one report at a time and preview it before
committing it to print. This can be handled slightly differently, as in
my example.
<aircode>
Function Set_PageSetup(Target As Worksheet, _
LHdrText As String) As Boolean
On Error GoTo ErrExit
With Target.PageSetup
.PrintArea = Selection.Address
.PrintTitleRows = "$1:$1"
.LeftHeader = LHdrText
.RightHeader = myDate
.FitToPagesWide = 1
End With
ErrExit:
Set_PageSetup = (Err = 0)
If Not Set_PageSetup Then wks.CenterHeader = "": wks.PrintArea = ""
End Function
Example usage:
Sub PrintReports()
Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
For Each wks In ActiveWindow.SelectedSheets
If Set_PageSetup(wks, wks.Range("LHdrText")) Then
.PrintOut Preview:=True
wks.CenterHeader = "": wks.PrintArea = ""
Else
MsgBox "An error occured doing PageSetup for sheet '" _
& wks.Name & "'!"
End If
End Sub
The code above will only execute if Set_PageSetup was successful (ergo
no errors), and notify if any failures. It also implies that the text
for LeftHeader is stored in the same location on each sheet, AND that
cell is named "LHdrText" with local scope. (In the namebox to the left
of the FormulaBar, type an apostrophe, the sheetname, another
apostrophe, the exclamation character, then "LHdrText". Do this for
each sheet.)
NE##) and just used