CODEWITHSUNDEEP
excelvba
TechGeek
TechGeek

Reputation: 2212

Excel VBA - Specify Range as Page Header

I want to specify a Range as Page Header from my VBA Code.

But when I did

ActiveSheet.PageSetup.LeftHeader = HeaderSheet.Range("A1:L2").Value

I takes only first cell i.e. A1 from HeaderSheet as Page Header and ignores the rest. Also it doesn't apply border in Page Header.

Any workaround for this please?

Upvotes: 0

Views: 13665

Answers (1)

Lorin S.
Lorin S.

Reputation: 762

So the "Left Header" would be the left-most header for three headers that describe the sheet or report. Normally it would have values such as the report title, author, date of report, etc.

Are you sure you don't mean to indicate the row or column headers?

Below, the PrintTitleRows specifies rows 1 & 2 to be used as rows to repeat at the top. Alternatively columns to repeat on the left side is just the "A" column.

Sub example()

    'ActiveSheet.PageSetup.LeftHeader = HeaderSheet.Range("A1:L2").Value

    With ActiveSheet.PageSetup
       .PrintTitleRows = "$1:$2"
       .PrintTitleColumns = "$A:$A"
    End With
End Sub

If you did actually intend to put the contents of cells A1-L2 into the left-most header section then you'll have to do a concatenation of the cells, I believe... viz,

With ActiveSheet.PageSetup
    .LeftHeader = Range("a1").Value & " " & Range("b1").Value & " " & Range("a2").Value & " " & Range("b2").Value
End With

Upvotes: 1

Related Questions