Reference cell value to print in document header

[inddon]

Hello There,

I am using the below code to print the information in a document print header. It is running to an error. Could you please review and please advice?

Thanks
Don

Private Sub Workbook_BeforePrint(Cancel As Boolean)
  Dim ws As Worksheet
  Set ws = Sheets("Setup")
  ws.PageSetup.CenterHeader = ws.Range("N5").Value & " " & ws("N6").Value & Chr(10) & _ ws.Range("N7").Value
End Sub

 

[Belleke]

Hi,
This works for me.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("Setup")
  .PageSetup.CenterHeader = .[N5] & " " & .[N6] & Chr(10) & .[N7]
End With
End Sub

 

[inddon]

Hi,
This works for me.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With Sheets("Setup")
  .PageSetup.CenterHeader = .[N5] & " " & .[N6] & Chr(10) & .[N7]
End With
End Sub

Hi Belleke,

Thank you for your reply. Your code works fine when I am printing the worksheet "Setup"

I would like to reference another worksheets ("Setup") cell value whilst printing any worksheet.

In the below how can I refer to the value from worksheet "Setup"?

PrivateSub Workbook_BeforePrint(Cancel AsBoolean)
   Dim ws As Worksheet
   
   'Set the worksheet from where the value needs to be picked up
   Set ws = Sheets("Setup")

  ws.PageSetup.CenterHeader = ws.Range("N5").Value & " " & ws("N6").Value & Chr(10) & _ ws.Range("N7").Value
EndSub

Thanks & regards,
Don

 

[Belleke]

This code will print the active sheet with the N5,N6,N7 values of the Setup sheet, is this what you want?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Set ws = Worksheets("Setup")
ActiveSheet.PageSetup.CenterHeader = ws.[N5] & " " & ws.[N6] & Chr(10) & ws.[N7]
End Sub

 

[inddon]

Thank you. It works perfect

 

[Belleke]

Glad I could help, cheers.