Pasting a range of cells in Sheet Header

[Marlonoire]

Hi All,

I'm trying to use a VBA macro to copy the values from a small range of cells (A37:B42) into the Left Box of the header of a worksheet for printing.

I've been successful with a single cell using the following:

Sub UpdateHeader()
ActiveSheet.PageSetup.Leftheader = Range("A1").Value
End Sub

Try as I might I cant seem to find a way to get the values from the range of cells into the header.

Probably very simple, but any help would be appreciated

Thanks

Marlonoire

[mdmackillop]

Welcome to VBAX

Something like this perhaps
Set r = Cells(37, 1).CurrentRegion
For j = 1 To 5
For i = 1 To 2
txt = txt & r(j, i) & vbTab
Next
txt = txt & vbCr
Next
ActiveSheet.PageSetup.LeftHeader = txt

[Marlonoire]

Thanks MD

That works a treat, now if only I could change the 255 character limit for header cells!