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
            If ActiveSheet.Range("A2") = "" Then
                Application.CutCopyMode = Fasle
                ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select
            Application.CutCopyMode = Fasle
            End If
        ActiveSheet.Range("$A$" & RngRow).Select

    End If
    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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your open Feedback only related to this Blog Posts. Please note that I cannot respond to the Anonymous Comments.

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts