Friday, February 12, 2016

Worksheet Change Event to Consolidate the Data from Multiple Sheets

Worksheet Change Event to Consolidate the Data from Multiple User Sheets to one Main Sheet 

Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim MainTab As Object
    On Error GoTo ErrorHandle:

    Set MainTab = ThisWorkbook.Sheets("Main")
    Set UserTab = ThisWorkbook.Sheets("User_1")

    If Intersect(Target, Range("A:E")) Is Nothing Then
        Exit Sub

    ElseIf Target.Value = "Updated" Then

            RngRow = Target.Row
            Range("$A$" & RngRow & ":$D$" & RngRow).Copy
        MainTab.Activate
            If ActiveSheet.Range("A2") = "" Then
                ActiveSheet.Range("A2").Select
                ActiveSheet.Paste
                ActiveSheet.Range("A1").Select
                Application.CutCopyMode = Fasle
            Else
                ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select
                ActiveSheet.Paste
                ActiveSheet.Range("A1").End(xlDown).Select
            Application.CutCopyMode = Fasle
            End If
        
        UserTab.Activate
        ActiveSheet.Range("$A$" & RngRow).Select

    End If
ErrorHandle:
    If Err.Number = 13 Then Exit Sub

End Sub

Example :
Suppose we have the user sheets  with data as follows...

The above Worksheet Event executes when you selected the Entry_Status as "Updated", and the data from the respective sheet is Consolidated to Main sheet, as follows..


--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog