Solved

Update master page from other tabs in Excel 2007

Posted on 2016-10-04
8
54 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 47

Expert Comment

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

Author Comment

by:mel200
ID: 41828924
0
 
LVL 47

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
Independent Software Vendors: 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 33

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 47

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 47

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

Industry Leaders: 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!

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…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

738 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