Hello

I've scanned several posts, but can't quite find a solution to my problem.

I often need to print a report (worksheet PrntRpt) with a left header that
gets its value from another worksheet (Master). Here's my problem - the
header has 3 lines that get their values from cells A1, A2, A3 of worksheet
Master. I can manually enter this as a 3 line custom header , but I'd
like to do it programatically.

Any ideas?

Much thanks
Brian

Hi Brian,

I'm assuming from your question that you know some macro basics.
If you are already printing from a button incorporate the following
into your existing code:

Dim CustomLeftHeader As String
CustomLeftHeader = Worksheets("Master").Range("a1") & Chr(13) _
& Worksheets("Master").Range("a2") & Chr(13) &
Worksheets("Master").Range("a3")
With Worksheets("PrntRpt").PageSetup
..LeftHeader = CustomLeftHeader
End With


Or if you/users will just be printing using Excel's normal methods (eg
[ctrl + p], file - print) enter this code into your ThisWorkbook code
sheet:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim CustomLeftHeader As String
If ActiveSheet.Name = "PrntRpt" Then
CustomLeftHeader = Worksheets("Master").Range("a1") & Chr(13) _
& Worksheets("Master").Range("a2") & Chr(13) &
Worksheets("Master").Range("a3")
With Worksheets("PrntRpt").PageSetup
..LeftHeader = CustomLeftHeader
End With
Else
End If
End Sub


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=521424

Thank you, Rob
It needed a dot before the LeftHeader, but otherwise works fine.
Brian

"broro183" wrote in
message ...

Hi Brian,

I'm assuming from your question that you know some macro basics.
If you are already printing from a button incorporate the following
into your existing code:

Dim CustomLeftHeader As String
CustomLeftHeader = Worksheets("Master").Range("a1") & Chr(13) _
& Worksheets("Master").Range("a2") & Chr(13) &
Worksheets("Master").Range("a3")
With Worksheets("PrntRpt").PageSetup
LeftHeader = CustomLeftHeader
End With


Or if you/users will just be printing using Excel's normal methods (eg
[ctrl + p], file - print) enter this code into your ThisWorkbook code
sheet:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim CustomLeftHeader As String
If ActiveSheet.Name = "PrntRpt" Then
CustomLeftHeader = Worksheets("Master").Range("a1") & Chr(13) _
& Worksheets("Master").Range("a2") & Chr(13) &
Worksheets("Master").Range("a3")
With Worksheets("PrntRpt").PageSetup
LeftHeader = CustomLeftHeader
End With
Else
End If
End Sub


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile:
http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=521424

Hi Brian,

Thanks for the feedback :-)

It's strange about the "dot before leftheader" as I can see it in my
original post but not in your quoted version. Oh well, problem solved
now.

Rob Brockett
NZ
Alweays learning & the best way to learn is to experience...


--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=521424