Cell value in Header - different for each worksheet and macro to loop

Question

I have reports that I work on each month and have to change the headers. I have 20 worksheets in the workbook, and each worksheet needs to have different headers.

I wanted to create a macro for header, that would reference to 4 cells in each worksheet.

eg - worksheet one - Q115 vs Q414 - Header needs to reference cell a150,a151,a152,a153. The cell a152 has a formula in it. FY15 vs FY14 worksheet would refer to cells a150,a151,a152,a153 within itself, so on ... I created a macro after googling but once I run it all the worksheets have the header from the first active worksheet.

Can you please help me correct it?

Sub InsertHeaderFooter()

Dim sheetIndex, numsheets As Integer
sheetIndex = 1
numsheets = Sheets.Count
' Loop through each sheet, but don't set any of them to active
While sheetIndex <= numsheets
    Dim sheetname, role, labeltext As String
    sheetname = Sheets(sheetIndex).name
    role = GetRole(mode) 
    labeltext = "Some text - " & sheetname & " - " & role
 strong text        
    With Sheets(sheetIndex).PageSetup
        .LeftHeader = ""
        .CenterHeader = vbCr & Range("A150").Text & vbCr & Range("A151").Text & _
            vbCr & Range("A152").Text & vbCr & Range("A153").Text
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "SanDisk Confidential"
        .RightFooter = "&[Date] - &[Time]"
    End With
    sheetIndex = sheetIndex + 1
Wend
End Sub

Your posted code doesn't align with your question. Please illustrate what you want to achieve (provide screen shots and links) and provide the code you use which fails. — L42
Hello, I apologize but I am new to vba .. why do you think they dont sink ? — Meg

nwhaught nwhaught · Accepted Answer · 2015-03-13T22:18:36

Change

.CenterHeader = vbCr & Range("A150").Text & vbCr & Range("A151").Text & _
            vbCr & Range("A152").Text & vbCr & Range("A153").Text

to

  .CenterHeader = vbCr & .Range("A150").Text & vbCr & .Range("A151").Text & _
        vbCr & .Range("A152").Text & vbCr & .Range("A153").Text

Range makes a reference to the active sheet.

.Range makes a reference to the With Sheets(sheetIndex) sheet.

Cell value in Header - different for each worksheet and macro to loop

1 Answers