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..
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...
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------