• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

Update master page from other tabs in Excel 2007

Hi, I have an excel spreadsheet with 6 tabs. I've created a seventh tab called Master. They all have the same headings and columns.

I'd like the master tab to contain everything from all the other tabs, and also to update when another row is added to another tab.

So if some adds a new row to tab one, a new row will be created in the Master tab and the identical data will be input.

I tried this: http://smallbusiness.chron.com/insert-data-multiple-excel-spreadsheets-one-excel-spreadsheet-75667.html  and it didn't work, it added the headers but then just some random info, mostly the zip codes. The few phone numbers that were added to the master sheet weren't formatted correctly, and reformatting the cells before consolidating didn't help that problem.

Can someone help? Thanks!

tabs are named:

Snr Nutrition, Hosp, Snt Ctrs, Renal Diet, HomeCare, Rehab, Master -New. They can be renamed (like Renal_Diet) if it's easier.
  • 4
  • 3
1 Solution
Martin LissOlder than dirtCommented:
A sample workbook would help.
mel200Author Commented:
Martin LissOlder than dirtCommented:
A few questions:

1) Do you want the Master sheet to show the "Activity" data?

2) If the user changes a cell in one of the non-Master tabs, do you want that change to show up in the Master?

3) If the answer to 2 is "yes" how do I know where the data fora particular Master row came from? Is the data on each non-Master tab unique? I suppose I could just clear the Master tab any time a change is made to any non-Master tab and repopulate the Master tab from top to bottom.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Rob HensonFinance AnalystCommented:
I have seen similar solutions but with the movement of data going the other way.

The Master sheet is updated manually and then a routine runs to populate/create individual tabs based on a filter value from the Master.
mel200Author Commented:
Hi, Martin,

I would say yes to both questions, although the change to any cell would be rare. Thanks for looking at this.
Martin LissOlder than dirtCommented:
Add this code to the sheet that you want to use as the master.
Private Sub Worksheet_Activate()

    Dim ws As Worksheet

    ' Refresh the sheet from data on all the other sheets
    Application.ScreenUpdating = False
    With ActiveSheet
        ' Totally clear the sheet
        ' Copy the headings from the first sheet
        Sheets("Snr Nutrition").Rows(1).Copy Destination:=.Rows(1)
        ' Copy each sheet's data to this sheet
        For Each ws In Worksheets
            Select Case ws.Name
                Case ActiveSheet.Name, "OLD-MASTER"
                    ' skip these sheets
                Case Else
                    ' Copy everything below the heading to the bottom of this sheet
                    ws.UsedRange.Cells.Offset(1, 0).Copy Destination:=.Rows(.Range("A1048576").End(xlUp).Row + 1)
            End Select
    End With
    Application.ScreenUpdating = True
End Sub

Open in new window

mel200Author Commented:
You are literally a genius. Thanks so much.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help, and that you for the compliment.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now