Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Update master page from other tabs in Excel 2007

Posted on 2016-10-04
8
Medium Priority
?
72 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 49

Expert Comment

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

Author Comment

by:mel200
ID: 41828924
0
 
LVL 49

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

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
 

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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

971 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