Refresh master sheet to reflect changes in other sheets

SankyoShari · Apr 29, 2008

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 · Apr 29, 2008

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 · Apr 29, 2008

This is my combine macro.

Code:

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 · Apr 29, 2008

Here's some generic code:

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

SankyoShari · Apr 30, 2008

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 · Apr 30, 2008

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 · Apr 30, 2008

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 · Apr 30, 2008

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 · Apr 30, 2008

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 · Apr 30, 2008

Here you go:

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

Refresh master sheet to reflect changes in other sheets

SankyoShari

New Member

Excel Facts

Smitty

Legend

SankyoShari

New Member

Smitty

Legend

SankyoShari

New Member

joed

Board Regular

SankyoShari

New Member

Smitty

Legend

SankyoShari

New Member

Smitty

Legend

Similar threads

Forum statistics

Share this page

Refresh master sheet to reflect changes in other sheets

New Member

Excel Facts

Legend

New Member

Legend

New Member

Board Regular

New Member

Legend

New Member

Legend

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock