Previous Topic Next Topic

Auto copy and paste cell range to a separate sheet

Solved/Closed
Report
Posts
3
Registration date
Friday December 30, 2016
Status
Member
Last seen
December 30, 2016
 -
Posts
3
Registration date
Friday December 30, 2016
Status
Member
Last seen
December 30, 2016
 -
Hello,

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.

Thank you in advance!!

1 reply

Answer 1 / 1

Try to initialize a variable as a RAnge as in:
Dim TheseCellValues as Range
TheseCellValues="a1:a10"
ActiveSheet.Range(TheseCellValues).Select

See if that help get you closer.
Report
Posts
3
Registration date
Friday December 30, 2016
Status
Member
Last seen
December 30, 2016
Would I insert this into my current code or replace a part of it with the new piece?
Report
Blocked Profile
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!
Report
Posts
3
Registration date
Friday December 30, 2016
Status
Member
Last seen
December 30, 2016
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.
Ask a question