Solved

Update master page from other tabs in Excel 2007

Posted on 2016-10-04
8
49 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
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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

740 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