Creating a row that is constant and updatable across all worksheet

  • Thread starter Global Sys Admin
  • Start date
G

Global Sys Admin

I have a workbookwith several worksheets in it. I want to create a type of
header row that remains constant across all worksheets, but can be updated in
all by only updating one item. What is the best way to accomplish this, do I
need a type of master list in the same workbook?
 
S

Sean Timmons

It can be done. How depends on what you're looking to do.

You can always use VB code thatr views your updated item and will run either
an If Then r a Case Select depending on the number of possible options.

But if it's relatively simple, you can just do If statements in your header
rows to indicate what you want them to say. Copy and paste from one extrnal
sheet to all the others to achieve the same effect.

Perhaps if you give a bit more detail?
 
T

tedmi

I have implemented this in this fashion:
On Sheet1, I enter the common values in Row 1.
On each of the other sheets, I enter this in cell A1:
=Sheet1!A1
and copy across for as many columns as necessary.
If I want to change the common header, I do it on Sheet1, and it
automatically propagates to all other sheets.
 
S

Sheeloo

One way is to to
1. copy the heading in A1 of Sheet 1 to A1 of Sheet 2, enter this in A1 of
Sheet 2
=Sheet1!A1
2. You can drag that to cover all the headings
3. Then you can copy the first row and paste in each of the Sheets

You can write a code which can loop through the sheets and paste the header
in Sheet1 to other sheets...(Change F in A1:F1 to the last Col letter in your
range

Sub CopyHeaderToAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Sheet1" Then
On Error Resume Next 'Will continue if an error results
Sheets("Sheet1").Select
Range("A1:F1").Select
Selection.Copy
ws.Range("A1").Select
' Range("A1").Select
ws.Paste
ws.Range("A1").Select
End If
Next ws
End Sub
 
G

Global Sys Admin

Thank you for the info, the first part of this works fine. However, if I
used the looping method, how would I loop through all the tabs if I've
changed the names from Sheet1, Sheet2, etc.?
 
G

Gord Dibben

In the code change "Sheet1" to the name of the sheet with the headings.

Or change to Sheets(1) which is the codename of first sheet.

No need to do anything else.

For Each ws In ActiveWorkbook.Worksheets doesn't care about sheetnames


Gord Dibben MS Excel MVP
 
Top