Header concern

  • Thread starter Thread starter Bri
  • Start date Start date
B

Bri

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...
 
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...
 
Back
Top