Solved Moving Rows to another Sheet

March 8, 2016 at 03:02:32
Specs: Windows 7
I have a table (A1:G5000) that is on Sheet1. Data begins in Row 5 and is continuous, but may end before Row 5000. The data was constructed using Excel formulas.
Based on a condition in Col G, I want to copy its Row to Sheet2 starting at Row 5. I need to copy only the data, not the formulas. Also it would be preferable to exclude Col G, copying only Ax:Fx.
Many thanks!

See More: Moving Rows to another Sheet

Report •


✔ Best Answer
March 10, 2016 at 06:29:17
Obviously, I can't see what you are doing from where I am sitting, so all I can do is tell you what you should be doing so that the macro will work. This process works for me and does not produce any errors. If you are unable to follow the steps in this process, please explain why so that I understand exactly what you are doing/trying to do.

1 - You will need a workbook with at least 2 worksheets.
2 - One of those worksheets needs to be named Sheet2. If the name of the worksheet that you are trying to copy the data to is not named Sheet2, then change both instances of "Sheet2" in the code to match the actual sheet name. The quotes are required.
3 - Select the code from Response #5 and copy it to your clipboard.
4 - Right click the sheet tab for the sheet in which you are entering OK in Column G.
5 - Choose "View Code" from the pop up list
6 - Paste the code into the pane that opens. Edit the "Sheet2" name if required, as noted in Step 2.

After following that process, you should be able to enter OK in any single cell in Column G of that sheet and the values in A:F of that Row will be copied to Sheet2.

It works for me every time.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
March 8, 2016 at 05:31:54
There are few things that are not clear to me:

1 - Based on a condition in Col G

Obviously, knowing that condition would help.

2 - copy its Row to Sheet2 starting at Row 5

Does the row that gets copied to Sheet2!Row5 replace the existing Row 5 or is it inserted at Row 5 moving all the other rows down or does it go at the bottom of the existing data in Sheet2?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
March 8, 2016 at 06:27:17
Col G condition is "OK" - if Col Gx="OK" then it gets moved.
Sorry, anything in Sheet2 is overwritten

Report •

#3
March 8, 2016 at 08:54:32
re: "Sorry, anything in Sheet2 is overwritten"

That still doesn't tell me where the data should be pasted. For now, I'm assuming it always gets pasted to Sheet2!Row5. If that is incorrect, please be specific about where the data should be pasted.

Right click the sheet tab for the sheet you want this to happen in and chose "View code".

Paste this code into the pane that opens, then enter OK anywhere in G5:G5000.

EDIT: Added xlPasteValues

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("G5:G5000")) Is Nothing Then
     If Target = "OK" Then
          Range("A" & Target.Row & ":F" & Target.Row).Copy
          Sheets("Sheet2").Range("A5").PasteSpecial Paste:=xlPasteValues
     End If
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
March 9, 2016 at 11:27:23
The rows that are pasted from Sheet1 to Sheet2 start at row 5, then 6, etc.
Setting this up as a Module, I'm getting an error "Expect End Sub"
Thanks.

Report •

#5
March 9, 2016 at 14:25:01
re: "Setting this up as a Module, I'm getting an error "Expect End Sub""

I have no idea what you mean by "Setting this up as a Module".

VBA code is stored in a Module. Event driven macros, such as a Worksheet_Change macro, must be stored in the Module for the Sheet in which the event will occur. In this case, the code must be stored in the Sheet1 Module so that it can monitor changes made to Sheet1 .

If you are getting an Expected End Sub error, then you must not have included an End Sub instruction at the end of the macro. Perhaps you didn't copy everything that I posted in my previous response?

This code should start pasting your data in Row 5 and increment 1 Row for each Paste operation.


Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("G5:G5000")) Is Nothing Then
     If Target = "OK" Then
        nxtRw = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
         If nxtRw < 5 Then nxtRw = 5
          Range("A" & Target.Row & ":F" & Target.Row).Copy
          Sheets("Sheet2").Range("A" & nxtRw).PasteSpecial Paste:=xlPasteValues
     End If
   End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
March 10, 2016 at 00:16:07
I'm getting "Run Time Error 424 - Object Required" with the debugger pointing to If Not Intersect....
Again, thanks.

Report •

#7
March 10, 2016 at 06:29:17
✔ Best Answer
Obviously, I can't see what you are doing from where I am sitting, so all I can do is tell you what you should be doing so that the macro will work. This process works for me and does not produce any errors. If you are unable to follow the steps in this process, please explain why so that I understand exactly what you are doing/trying to do.

1 - You will need a workbook with at least 2 worksheets.
2 - One of those worksheets needs to be named Sheet2. If the name of the worksheet that you are trying to copy the data to is not named Sheet2, then change both instances of "Sheet2" in the code to match the actual sheet name. The quotes are required.
3 - Select the code from Response #5 and copy it to your clipboard.
4 - Right click the sheet tab for the sheet in which you are entering OK in Column G.
5 - Choose "View Code" from the pop up list
6 - Paste the code into the pane that opens. Edit the "Sheet2" name if required, as noted in Step 2.

After following that process, you should be able to enter OK in any single cell in Column G of that sheet and the values in A:F of that Row will be copied to Sheet2.

It works for me every time.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question