I've been pondering about this problem. To simply put, I want to automate making Executive Summary for our company. I want to add Right Header on pages 2 onwards while leaving the first page with blank header. The problem is, the text I want to use is a combination of text from different references. I did concatenate it into a single cell on another sheet and I want to use that cell as the reference for my header. Can someone help me on this. Thank you so much in advance.

I tried vba using this code

ActiveSheet.PageSetup.DifferentFirstPageHeaderFooter = True
ActiveSheet.PageSetup.RightHeader = "the text I want to reference"
ActiveSheet.PageSetup.FirstPage.CenterHeader.Text = ""

if I change the RightHeader

ActiveSheet.PageSetup.RightHeader = otherSheet.Range("A2").Value

it returns an error. Run-time error '1004': "Unable to set the RightHeader property of the PageSetup class"

I've been pondering about this problem. To simply put, I want to automate making Executive Summary for our company. I want to add Right Header on pages 2 onwards while leaving the first page with blank header. The problem is, the text I want to use is a combination of text from different references. I did concatenate it into a single cell on another sheet and I want to use that cell as the reference for my header. Can someone help me on this. Thank you so much in advance.

I tried vba using this code

ActiveSheet.PageSetup.DifferentFirstPageHeaderFooter = True
ActiveSheet.PageSetup.RightHeader = "the text I want to reference"
ActiveSheet.PageSetup.FirstPage.CenterHeader.Text = ""

if I change the RightHeader

ActiveSheet.PageSetup.RightHeader = otherSheet.Range("A2").Value

it returns an error. Run-time error '1004': "Unable to set the RightHeader property of the PageSetup class"

• excel
• vba
• excel-2019

• 1 Did you check carefully what type of variable otherSheet? or do you set proper sheet to otherSheet? – Harun24hr Commented 23 hours ago
• @Harun24hr If sheet doesn't exist it is an Object required error for me. – Black cat Commented 23 hours ago
• @Blackcat Yes. OP's codes seems okay but he is getting error. – Harun24hr Commented 23 hours ago
• I googled some and maybe printer driver can be the problem. Restart your PC. – Black cat Commented 23 hours ago
• 1 I encountered problems when the total length of left plus middle plus right header text gets too long. – FunThomas Commented 20 hours ago

1 Answer 1

Below codes works to me where Sheet2 C1 cell has context Test Header.

ActiveSheet.PageSetup.DifferentFirstPageHeaderFooter = True
ActiveSheet.PageSetup.RightHeader = Sheets("Sheet2").Range("C1").Text
ActiveSheet.PageSetup.FirstPage.CenterHeader.Text = ""