Solved

Update master page from other tabs in Excel 2007

Posted on 2016-10-04
8
28 Views
Last Modified: 2016-10-05
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.
0
Comment
Question by:mel200
  • 4
  • 3
8 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41828870
A sample workbook would help.
0
 

Author Comment

by:mel200
ID: 41828924
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41829069
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.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 41829470
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:mel200
ID: 41829966
Hi, Martin,

I would say yes to both questions, although the change to any cell would be rare. Thanks for looking at this.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41830081
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
        .Cells.Clear
        ' 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
        Next
            
    End With
    
    Application.ScreenUpdating = True
End Sub

Open in new window

1
 

Author Closing Comment

by:mel200
ID: 41830162
You are literally a genius. Thanks so much.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41830224
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
1

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now