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
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.