VBA Macro - Automatic Update "Master" data sheet from multiple sheet

[fahadalambd]

Good Afternoon everyone,

Hope you are doing well. As you see the attached pics -

"Master Data" is my main data sheet.

I want a VBA script which will automatic update and move data in "Master" data sheet from Sheet1, Sheet2 and Sheet3.

It means if I change "Status" column in Sheet1, Sheet2 and Sheet3 then it will also change in "Master" data sheet.

NB. My actual Report has more than 10000 rows.

Thanks in advance

 

[Trebor76]

Hi there,

Put this into a standard module (mine was called Module1)...

Option Explicit
Sub UpdateStatusMaster(ID As Long, strStatus As String)

    Dim wsMaster As Worksheet
    Dim lngMatchRow As Long
    
    Set wsMaster = ThisWorkbook.Sheets("Master")
    
    lngMatchRow = Evaluate("IFERROR(MATCH(" & ID & ",'" & wsMaster.Name & "'!A:A,0),0)")
    
    If lngMatchRow > 0 Then
        wsMaster.Range("B" & lngMatchRow).Value = strStatus
    End If

End Sub

...and this worksheet event macro on each sheet that will you want to update the Master sheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrTrap

    If Target.Column = 2 And Target.Row >= 2 Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        
        Call Module1.UpdateStatusMaster(Target.Offset(0, -1).Value, Target.Value) '<-Module1 is the name of the module that houses the 'UpdateStatusMaster' macro. Change to suit if necessary.
        
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    
    End If
    
Exit Sub

ErrTrap:

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert

 

[fahadalambd]

Hi there,

Put this into a standard module (mine was called Module1)...

Option Explicit
Sub UpdateStatusMaster(ID As Long, strStatus As String)

    Dim wsMaster As Worksheet
    Dim lngMatchRow As Long
   
    Set wsMaster = ThisWorkbook.Sheets("Master")
   
    lngMatchRow = Evaluate("IFERROR(MATCH(" & ID & ",'" & wsMaster.Name & "'!A:A,0),0)")
   
    If lngMatchRow > 0 Then
        wsMaster.Range("B" & lngMatchRow).Value = strStatus
    End If

End Sub

...and this worksheet event macro on each sheet that will you want to update the Master sheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrTrap

    If Target.Column = 2 And Target.Row >= 2 Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
       
        Call Module1.UpdateStatusMaster(Target.Offset(0, -1).Value, Target.Value) '<-Module1 is the name of the module that houses the 'UpdateStatusMaster' macro. Change to suit if necessary.
       
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
   
    End If
   
Exit Sub

ErrTrap:

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert

Hi Robert,

Thanks for the reply but this not the answer what I want.

For example, as you in the pic - I want to move the "Status" of the ID 111 (which is "Done") into the "Master Data Sheet".

Please let me know if you know how to solve it. It will be really helpful me.

Thanks

 

[Trebor76]

For example, as you in the pic - I want to move the "Status" of the ID 111 (which is "Done") into the "Master Data Sheet".

So my code will copy the status type to the Master tab as long as there's a corresponding ID. If there is do you then want the row to be deleted from the originating sheet i.e. Sheet1 for example?

 

[kevin9999]

If it's purely the status of the ID that you want returned, why not just use a VLOOKUP() formula instead of VBA?

VLOOKUP.xlsx
	A	B
1	ID	Status
2	111	Done
3	112	Done
4	113	Not Done
5	114	Not Done
6	115	Done
7	116	Done
8	117	Withdraw
9	118	Withdraw
10	119	Withdraw
11	120	Done
12	121	Done
13	122	Not Done
14	123	Not Done
15	124	Withdraw
16	125	Withdraw
17	126	Done
18	127	Done
19	128	Not Done
20	129	Not Done
21	130	Not Found
22	131	Withdraw
23	132	Done
24	133	Not Done
Master

Cell Formulas
Range	Formula
B2:B24	B2	=IFERROR(VLOOKUP(A2,Sheet1!$A:$B,2,0),IFERROR(VLOOKUP(A2,Sheet2!$A:$B,2,0),IFERROR(VLOOKUP(A2,Sheet3!$A:$B,2,0),"Not Found")))

VLOOKUP.xlsx
	A	B
1	ID	Status
2	111	Done
3	112	Done
4	113	Not Done
5	114	Not Done
6	115	Done
7
Sheet1

VLOOKUP.xlsx
	A	B
1	ID	Status
2	116	Done
3	117	Withdraw
4	118	Withdraw
5	119	Withdraw
6	120	Done
7	121	Done
8	122	Not Done
9	123	Not Done
10	124	Withdraw
11	125	Withdraw
Sheet2

VLOOKUP.xlsx
	A	B
1	ID	Status
2	126	Done
3	127	Done
4	128	Not Done
5	129	Not Done
6	131	Withdraw
7	132	Done
8	133	Not Done
Sheet3

 

[fahadalambd]

Thank you so much Kevin. I didn't think about "Vlookup" formula :D but it is working.

It is working perfectly.Thanks again :D

 

[kevin9999]

Happy to help & thanks for the feedback

 

[Trebor76]

Yes - thanks Kevin. Based on the thread title I instantly thought of a macro solution not a formula solution.