Insert cellS(!) text into header for printing

[dilettante]

Hello
I copied the basic listing and tryed to have it working for all the headers
but it works only for the first one. What do I wrong?
Thank you for helping

Sub InsertCellsDataInHeader()

With ActiveSheet
..PageSetup.LeftHeader = .Range("A1").Text
..PageSetup.CenterHeader = .Range("A2").Text
..PageSetup.RightHeader = .Range("A3").Text
End With

End Sub

 

[Nigel]

I assume you run this code before you print? If so it will only act on the
currently active sheet, other sheets will not be changed as you may expect?

If this is the problem, you might try.....

Sub InsertCellsDataInHeader()
Dim wS as Worksheet
For each wS in Activeworkbook.Worksheets
With wS
.PageSetup.LeftHeader = .Range("A1").Text
.PageSetup.CenterHeader = .Range("A2").Text
.PageSetup.RightHeader = .Range("A3").Text
End With
Next
End Sub

This will put the values A1, A2 and A3 from each sheet into the headers for
that sheet, but you may wish to have the same values, say from sheet1
appearing in every worksheet. In wich case use

Sub InsertCellsDataInHeader()
Dim wS as Worksheet
For each wS in Activeworkbook.Worksheets
With wS
.PageSetup.LeftHeader = Sheets("Sheet1").Range("A1").Text
.PageSetup.CenterHeader = Sheets("Sheet1").Range("A2").Text
.PageSetup.RightHeader = Sheets("Sheet1").Range("A3").Text
End With
Next
End Sub

 

[dilettante]

Hello,
Thank you for helping.
The first lkisting works well.
The second one (which is more useful then the first) have a Run Time Error 9
coming out when the procedure reaches the expression
".PageSetup.LeftHeader = Sheets("Sheet1").Range("A1").Text ".
I will try to understand the reason why.
Best regards

 

[dilettante]

Nigel,
now I have it: it works perfectly.
Thank you!
Regards

 

[Nigel]

You're welcome. Glad it works OK now