Refresh master sheet to reflect changes in other sheets

[SankyoShari]

I am running a macro to create a combined master summary sheet from data in several other sheets contained in one workbook. That is working fine. I need to create some kind of refresh macro so that when data in these sheets change the master sheet will change and update automatically. Any suggestions?

 

[Smitty]

Welcome to the Board!

You might want to post the code that you've got now, so we can see how you're doing it.

As for automating the process, you can probably use a Workbook_SheetChange event, but you'd need to give more specifics, like the range to evaluate for a change, what data should be copied and where to...

 

[SankyoShari]

This is my combine macro.

Sub Combine()
    Dim J As Integer
    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add ' add a sheet in first place
    Sheets(1).Name = "Combined"
    ' copy headings
    Sheets(2).Activate
    Range("A5").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
    ' work through sheets
    For J = 2 To Sheets.Count ' from sheet 2 to last sheet
        Sheets(J).Activate ' make the sheet active
        Range("A5").Select
        Selection.CurrentRegion.Select ' select all cells in this sheets
        ' select all lines except title
        Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
        ' copy cells selected in the new sheet on last line
        Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
End Sub

I'm not familiar with the Workbook_SheetChange event so that my work. Do you have the code so I can try it?

 

[Smitty]

Here's some generic code:

<font face=Tahoma><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A:A")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Specify which sheet(s) to exclude from action</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> ActiveSheet.Name = "Sheet1" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN><br>            <SPAN style="color:#007F00">'   Do your thing here</SPAN><br>            <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[SankyoShari]

I am very unfamiliar with macros and am not sure where to place this Workbook_SheetChange code or even how to change it for my needs. Currently there are 5 sheets. The first one is a combination of the other 4. When I make any change to worksheet CustomerA, CustomerB, CustomerC, or CustomerD then I want the Combined worksheet to reflect this. Sorry for my incompetence.

 

[joed]

Am I misunderstanding what you want but why don't you just make the cell in the summary sheet be a link to the data in the customer sheet. Copy the cell from the customer sheet and paste as link in the summary.

Cell in summary will like

='customer a'!C55

'customer a' being sheet name and !C55 being the cell to appear on summary. Anytime you change data in customer a sheet cell c55 the data on summary sheet will change.

 

[SankyoShari]

I guess I could do that, but eventually I will have close to 100 sheets each with 85 rows and 35 columns per sheet. That seems pretty labor intensive, I was hoping there was something that could just be ran automatically.

 

[Smitty]

A Sheet change event goes in the sheet specific module. But getting it to do what you want is another thing.

I recently did something like this for a co-worker who has 75 industry specific sheets, each detailing product ads for that industry. Any change in status will be updated on a summary sheet and new entries will be added. It does this by looking for a matching ad number on the Master sheet. If it finds it it copies the revised data over the existing, otherwise adds a new record.

I can post the code for that if you want, but if you're not familiar with VBA it will likely be over your head. If you can post examples of your sheets I may be able to adjust it or someone can come up with something similar. Just note I'll be in customer meetings the better part of the day so I'm not being rude if I don't get back to you right away.

 

[SankyoShari]

That is exactly what I need. If you could post the code I'll take a stab at it, if I can't get it then I'll post some more information and when you have a chance hopefully you can look at it. Thanks for your help.

 

[Smitty]

Here you go:

<font face=Tahoma><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range, LastRow <SPAN style="color:#00007F">As</SPAN> Range, AdNumberRange <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> AdNumber <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, FirstAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, CurrentSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <SPAN style="color:#007F00">'   Set Target Range</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("L:L")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Do not act if the active sheet is the master sheet</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Parent.Name <> "Master" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met</SPAN><br>            <br>            <SPAN style="color:#007F00">'   Apply Ad Status Code Formattting</SPAN><br>                <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Text<br>                    <SPAN style="color:#00007F">Case</SPAN> "On Order"<br>                        Range(Cells(Target.Row, "A"), Cells(Target.Row, "L")).Interior.ColorIndex = 6<br>                    <SPAN style="color:#00007F">Case</SPAN> "Sold"<br>                        Range(Cells(Target.Row, "A"), Cells(Target.Row, "L")).Interior.ColorIndex = 3<br>                    <SPAN style="color:#00007F">Case</SPAN> "Live"<br>                        Range(Cells(Target.Row, "A"), Cells(Target.Row, "L")).Interior.ColorIndex = 4<br>                    <SPAN style="color:#00007F">Case</SPAN> ""<br>                        Range(Cells(Target.Row, "A"), Cells(Target.Row, "L")).Interior.ColorIndex = 0<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>                <br>                <SPAN style="color:#007F00">'   Capture the original worksheet to return to it</SPAN><br>                CurrentSheet = ActiveSheet.Name<br>                <br>                <SPAN style="color:#007F00">'   Look for the Ad Number in the Master sheet</SPAN><br>                AdNumber = Cells(Target.Row, "A").Text<br>                Sheets("MASTER").Activate<br>                    <SPAN style="color:#00007F">With</SPAN> Sheets("MASTER").Range("A1:A65536")<br>                        <SPAN style="color:#00007F">Set</SPAN> c = .Find(AdNumber, LookIn:=xlValues, LookAt:=xlWhole)<br>                        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                            FirstAddress = c.Address<br>                            <SPAN style="color:#00007F">Do</SPAN><br>                                <SPAN style="color:#007F00">'   If the Ad Number is found, overwrite the existing data with the current data</SPAN><br>                                MsgBox c.Row<br>                                Target.EntireRow.Copy Sheets("MASTER").Range("A" & c.Row)<br>                                <SPAN style="color:#00007F">Set</SPAN> c = .FindNext(c)<br>                            <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> And c.Address <> FirstAddress<br>                        Else:<br>                            <SPAN style="color:#007F00">'   If the Ad Number isn't found then copy the new ad's data to the Master</SPAN><br>                            Set LastRow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp)<br>                               Target.EntireRow.Copy LastRow.Offset(1)<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <br>                <SPAN style="color:#007F00">'   Sort by Ad Status and Ad Number</SPAN><br>                    Range([A1], Cells(Rows.Count, "L").End(xlUp)).Sort Key1:=Range("L2"), Order1:=xlAscending, Key2:=Range _<br>                        ("B2"), Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending, _<br>                        Header:=xlGuess<br>                        <br>                    <SPAN style="color:#007F00">'   Return to original sheet & Sort by Ad Status</SPAN><br>                    Sheets(CurrentSheet).Activate<br>                    Range([A1], Cells(Rows.Count, "L").End(xlUp)).Sort Key1:=Range("L2"), Order1:=xlAscending, Header:= _<br>                        xlGuess<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>