Report

Auto copy and paste cell range to a separate sheet [Solved]

Ask a question bnoboa 3Posts Friday December 30, 2016Registration date December 30, 2016 Last seen - Last answered on Dec 30, 2016 at 05:21 PM by bnoboa
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!!
See more 
Helpful
+0
plus moins
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.
bnoboa 3Posts Friday December 30, 2016Registration date December 30, 2016 Last seen - Dec 30, 2016 at 01:01 PM
Would I insert this into my current code or replace a part of it with the new piece?
Reply
ac3mark 6639Posts Monday June 3, 2013Registration date ModeratorStatus March 13, 2017 Last seen - Dec 30, 2016 at 02:38 PM
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!
Reply
bnoboa 3Posts Friday December 30, 2016Registration date December 30, 2016 Last seen - Dec 30, 2016 at 05:21 PM
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.
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!