Cell Referencing In Header

  • Thread starter Thread starter kaken6
  • Start date Start date
K

kaken6

Hi, I've been trying to use a cell reference in the header of my excel
worksheets. The cell, B11, is in Worksheet1. I have the header
updated when the worksheet is opened. The code looks like:

Private Sub Worksheet_Activate()
Worksheets("Worksheet1").PageSetup.LeftHeader =
Range("Worksheet1'!B11").Value
End Sub

It works well for Worksheet1

But when I try to reference the same cell for my other worksheets with
code looking like:

Private Sub Worksheet_Activate()
Worksheets("Worksheet2").PageSetup.LeftHeader =
Range("Worksheet1'!B11").Value
End Sub

I get a runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Does anyone know how to correct this?
Also, is there some way I can make the cell reference italicized in
the header?
Thank you!
 
You're changing the header in the worksheet you just activated?

Private Sub Worksheet_Activate()
me.PageSetup.LeftHeader = me.range("B11").Value
End Sub

Me is the object that owns the code--in this case, the worksheet you just
activated.

If you're actually changing the header in a different worksheet:

Worksheets("Worksheet1").PageSetup.LeftHeader _
= worksheets("worksheet1").range("B11").Value

or to save some typing:

with Worksheets("Worksheet1")
.PageSetup.LeftHeader = .range("B11").Value
end with

Change that worksheet1 to the name you see on the tab in excel.
 
You're changing the header in the worksheet you just activated?

Private Sub Worksheet_Activate()
me.PageSetup.LeftHeader = me.range("B11").Value
End Sub

Me is the object that owns the code--in this case, the worksheet you just
activated.

If you're actually changing the header in a different worksheet:

Worksheets("Worksheet1").PageSetup.LeftHeader _
= worksheets("worksheet1").range("B11").Value

or to save some typing:

with Worksheets("Worksheet1")
.PageSetup.LeftHeader = .range("B11").Value
end with

Change that worksheet1 to the name you see on the tab in excel.













--

Dave Peterson- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you Dave,

Worksheets("Worksheet2").PageSetup.LeftHeader=
worksheets("Worksheet1").range("B11").Value
Works great.
Is there any way to italicize this reference?

Thanks again.
 
Sub test()
With Worksheets("Worksheet2").PageSetup
.LeftHeader = "&""Arial,Italic""&12" & _
Worksheets("Worksheet1").Range("B11").Value
End With
End Sub


Gord Dibben MS Excel MVP
 
Back
Top