Uhtred Bebbanburg
New Member
- Joined
- Apr 15, 2023
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
I'm trying to copy select rows from multiple sheets only when the value in column B is "Y".
I need to do this from multiple worksheets to one target worksheet without overwriting the values e.g. it will check for the last row that has a value and put the new entry in the row below.
I have managed to get this to work based on entering the "Y" value in column B of a particular worksheet. Code for this is:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
lastrow = Sheets("Change Tracker 2").Cells(Rows.Count, "A").End(xlUp).Row
If Target.Column = 2 And UCase(Target.Value) = "Y" Then
Cells(Target.Row, Target.Column).EntireRow.Copy Destination:= _
Sheets("Change Tracker 2").Range("A" & lastrow + 1).End(xlUp).Offset(1)
End If
errhandler:
Application.EnableEvents = True
End Sub
However the problem with this is if updates are made to the active sheet that is being copied from after the "Y" has been entered in column B for that row these changes won't show up in the target sheet that is being copied to, that sheet being "Change Tracker 2".
I therefore want to modify this code to work with a command button that can run through multiple named worksheets and copy the rows in the same fashion collating them all into the "Change Tracker 2" worksheet. Will also clear all the values from the "Change Tracker 2" worksheet before it does this so basically when the user wants to the can collate all of the rows that meet the criteria by pressing the button and they are all up to date.
Have tried this but don't know how to set the worksheets to copy from. Code I have so far is:
Sub Button2_Click()
On Error GoTo errhandler
Application.EnableEvents = False
lastrow = Sheets("Change Tracker 2").Cells(Rows.Count, "A").End(xlUp).Row
Set WorkRng = Sheets("General").Range("A7:M105")
If Target.Column = 2 And UCase(Target.Value) = "Y" Then
Cells(Target.Row, Target.Column).EntireRow.Copy Destination:= _
Sheets("Change Tracker 2").Range("A" & lastrow + 1).End(xlUp).Offset(1)
End If
errhandler:
Application.EnableEvents = True
End Sub
As you can see I've attempted (and failed) to set the working sheet to copy from but only one, will need multiple and haven't added the clear function for the sheet yet (though I should be able to manage this at least)
Hope this makes sense and if anyone can help would be much appreciated.
I need to do this from multiple worksheets to one target worksheet without overwriting the values e.g. it will check for the last row that has a value and put the new entry in the row below.
I have managed to get this to work based on entering the "Y" value in column B of a particular worksheet. Code for this is:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhandler
Application.EnableEvents = False
lastrow = Sheets("Change Tracker 2").Cells(Rows.Count, "A").End(xlUp).Row
If Target.Column = 2 And UCase(Target.Value) = "Y" Then
Cells(Target.Row, Target.Column).EntireRow.Copy Destination:= _
Sheets("Change Tracker 2").Range("A" & lastrow + 1).End(xlUp).Offset(1)
End If
errhandler:
Application.EnableEvents = True
End Sub
However the problem with this is if updates are made to the active sheet that is being copied from after the "Y" has been entered in column B for that row these changes won't show up in the target sheet that is being copied to, that sheet being "Change Tracker 2".
I therefore want to modify this code to work with a command button that can run through multiple named worksheets and copy the rows in the same fashion collating them all into the "Change Tracker 2" worksheet. Will also clear all the values from the "Change Tracker 2" worksheet before it does this so basically when the user wants to the can collate all of the rows that meet the criteria by pressing the button and they are all up to date.
Have tried this but don't know how to set the worksheets to copy from. Code I have so far is:
Sub Button2_Click()
On Error GoTo errhandler
Application.EnableEvents = False
lastrow = Sheets("Change Tracker 2").Cells(Rows.Count, "A").End(xlUp).Row
Set WorkRng = Sheets("General").Range("A7:M105")
If Target.Column = 2 And UCase(Target.Value) = "Y" Then
Cells(Target.Row, Target.Column).EntireRow.Copy Destination:= _
Sheets("Change Tracker 2").Range("A" & lastrow + 1).End(xlUp).Offset(1)
End If
errhandler:
Application.EnableEvents = True
End Sub
As you can see I've attempted (and failed) to set the working sheet to copy from but only one, will need multiple and haven't added the clear function for the sheet yet (though I should be able to manage this at least)
Hope this makes sense and if anyone can help would be much appreciated.