I am new to VBA/Macro with excel, but I want my code to automatically copy a range of cells in the same row that a change is made in a particular column and then that range pasted on the next empty row in another sheet of the same workbook. So far I have pieced together the following code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 54 Then
ThisRow = Target.Row
If Target.Value = "<>" Then
Range("U" & ThisRow & ":BK" & ThisRow).Select
Selection.Copy
Worksheets("Sheet2").Select
NextRow = Sheets("Sheet2").Range("U" & Rows.Count).End(xlUp).Row + 1
ActiveSheet.Cells(NextRow, 21).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
End If
End Sub
The code worked perfectly on one individual cell but I need it to automatically activate when any cell in column BB(54) is changed and copy the range of cells from column U(21):BK(63) of that row in which the cell of column BB(54) was changed, then paste that section to another worksheet in the next available empty row.
Please assist, I have been working on this code for 3 days.
Well, the DIm would be one of the first declarations. Then As you move to a new cell, that is where you would set up the new value for the variable you initialized. then call the variable where you want to paste. So, this solution cannot be cut and pasted into place, but should be used as a wire frame for the syntax of methods to accomplish what you are trying to do!
My hope is to have the worksheet automatically recognize any changes to the cells in column 54 as I use the document daily and to then copy the row that has the change and paste it into Sheet2 on the next available row.
- Posts
- 13031
- Registration date
- Monday June 3, 2013
- Status
- Moderator
- Last seen
- October 11, 2019
1575