Excel - Same headers across multiple Sheets

In Excel 2007, is it possible to setup headers (on row 1) and duplicate those headers across multiple Sheets without copying and pasting them into each Sheet?

So as you select the tabs (Sheet1, Sheet2, Sheet3) the column headers for each Sheet would be the same, and if you added a column it would show up in each Sheet (same for editing the name or removing a column).

Thank you for any help you can provide.
LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please follow these steps to achieve this....

1) Open your workbook.

2) Press Alt+F11 to open VBA Editor.

3) From the project explorer from the left side, double click ThisWorkbook.

4) Paste the given code into the opened code window.

5) Close the VBa Editor.

6) Save your workbook as Macro-Enabled Workbook.

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim ws As Worksheet
Application.EnableEvents = False
For Each ws In Worksheets
    Rows(1).Copy ws.Range("A1")
Next ws
Application.EnableEvents = True
End Sub

Open in new window

The above code assumes that your header row is row1. So now if you add any header in row1 of any sheet, the same header will be populated in all other sheets in the workbook.

Is this what you are trying to achieve?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
You can always add references to the other sheets, i.e. put  =Sheet1!A1   into A1 and drag to the right.
However, if you change the sequence of columns, and that includes deleting, header and values do not fit together anymore, so the request is questionable.
Robberbaron (robr)Commented:
sktneer comment only applies if you change a value in a header as it does the copy for you.

I cant see how deleting or inserting a column is detected, so cant apply any update.
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Learn More!
regmigrantCommented:
If this is during the setup process you can select multiple sheets by holding shift as you click on the tab names, all the changes you make on one sheet will be reflected in the others, however it will only work when all sheets are selected in a group so its not so useful when handing this out to end-users because they forget and things get out of step
tel2Commented:
Hi knowlton,

I'm not sure this is what you are after, but if you have changes that you want to happen to more than 1 sheet, you can:

1. Select all the sheet names you want changes to occur in (e.g. hold down Ctrl, click each of the sheet tabs down the bottom, then release Ctrl.  You can also use Shift to select ranges.  Similar to selecting files with Windows Explorer).  You should now see all sheet names are highlighted.
2. Make the required changes to the currently visible sheet (it shouldn't matter which sheet that is).
3. When you want to stop doing multi-sheet updates, click an unselected sheet name (if there is one), or any sheet if all are selected.  You should now see only 1 sheet name is highlighted.

This should work for your headings and column insertions.  Works for me in Excel 2007.
Robberbaron (robr)Commented:
maybe it can work, but assuems all your worksheets are the same configuration...

put this in the ThisWorkbook macro module.
Public OldColCount As Long
Const header = "1:1"
Private Sub Workbook_Open()
    OldColCount = ActiveSheet.UsedRange.Columns.Count
End Sub


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    
    If Intersect(Sh.Range(header), Target) Is Nothing Then
        'no change to header
        Exit Sub
    End If
    Debug.Print "Sheet chnaged:" & Sh.Name & "== range:" & Target.Address

    
    Application.EnableEvents = False  'turn of future events in the process
    
    Dim NewColCount As Long
    NewColCount = ActiveSheet.UsedRange.Columns.Count
    
    If NewColCount > OldColCount Then
        'column inserted.  Target is the blank column
        'insert this column to all worksheets
        colid = Target.Column
        For Each ws In ActiveWorkbook.Sheets
            If ws.Name = ActiveSheet.Name Then
                'do nothing
             Else
                ws.Columns(colid).Insert
            End If
        Next ws
        'reset col count
        OldColCount = NewColCount
     ElseIf NewColCount < OldColCount Then
        'column deleted.  target is column to the right of deleted column
        colid = Target.Column
        For Each ws In ActiveWorkbook.Sheets
            If ws.Name = ActiveSheet.Name Then
                'do nothing
             Else
                ws.Columns(colid).Delete
            End If
        Next ws
        OldColCount = NewColCount
     Else
        'was a simple header change
        'copy Target cell to same on all sheets
        For Each ws In ActiveWorkbook.Sheets
            If ws.Name = ActiveSheet.Name Then
                'do nothing
             Else
                Target.Copy Destination:=ws.Range(Target.Address)
            End If
        Next ws
    End If

    Application.EnableEvents = True
End Sub

Open in new window


note that it uses the ActiveSheet.UsedRange property that is unreliable.
header-update.xls
tel2Commented:
But I now see that regmigrant has already given a summary of what I was trying to say.  I must learn to type faster!
regmigrantCommented:
Tel did mention using CTRL so you can select sheets that aren't next to each other; faster typing not always best :)
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think I  missed the removing a column criteria, so tweaked the code and hope it will work as per your requirement now...

Place the following code into ThisWorkbook Module by following the instructions mentioned in my last post.
Option Explicit
Dim oH As String
Dim Actlc As Long
Dim col As Long

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ws As Worksheet
Dim lc As Long, i As Long
Dim hRnd As Range, Cell As Range
Application.EnableEvents = False
Actlc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

For Each ws In Worksheets
    If ws.Name <> ActiveSheet.Name Then
        For i = 1 To Actlc
            ws.Cells(1, i) = Cells(1, i)
        Next i
        lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
        If Actlc <> lc And oH <> "" Then
            If Cells(1, col) <> oH Then
                ws.Columns(col).Delete
            End If
        End If
    End If
Next ws
Application.EnableEvents = True
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Actlc = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
If Selection.Column <= Actlc Then
    Application.EnableEvents = False
    oH = Cells(1, Selection.Column)
    col = Selection.Column
    Application.EnableEvents = True
End If
End Sub

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.