Friday, 12 February 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..





Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts