Automatically move rows to another sheet

BradleyN1

New Member
Joined
May 5, 2017
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi

I have a spreadsheet of people who attended training

So for example I have 'Sheet 1' with peoples information on, let's say Column B represents 'completed', if entered in this column 'completed' I want that row of information to be transferred automatically to 'Sheet 2' (which would represent a completed training sheet) and along with that delete the blank row it was originally on, on 'Sheet 1'

The two sheets within the same workbook will have the same layout and headings etc. but 'Sheet 2' will basically be a blank worksheet for the info to be transferred onto

Also as the information is being transferred, can the rows which are being moved over fall under each row that has been transferred over (rather than overwriting what has already been transferred)

I hope my explanation suffices but if not please just ask me anymore info, I greatly appreciate any help :)

P.S I've had a browse on an already existing thread but there's so many pages and people have had errors and it's been edited as the pages go on so I don't know which one actually works, so apologies for that!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try putting the code below in Sheet1's worksheet module (right click the sheet tab and click view code).

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "completed" Then
            With Target.EntireRow
                .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
    End If
End Sub


The code will start "pasting" from Row 2 if you don't have headers and assumes you always have data in column A.
 
Last edited:
Upvote 0
Try putting the code below in Sheet1's worksheet module (right click the sheet tab and click view code).

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 And Target.Cells.Count = 1 Then
        If LCase(Target.Value) = "completed" Then
            With Target.EntireRow
                .Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                .Delete
            End With
        End If
    End If
End Sub


The code will start "pasting" from Row 2 if you don't have headers and assumes you always have data in column A.

You are fab!! This works great and I think I have an idea on how to tweak it a little if I need to
Thank you SO MUCH
 
Upvote 0
You're welcome and welcome to the forum.
 
Last edited:
Upvote 0
You're welcome and welcome to the forum.

Thanks Mark :)
One thing- what part needs editing say, if I wanted it to begin from B4 (the rows above are headings etc. and A is just blank which don't need copying)
Again, sheet 2 would be the exact copy

thanks!
 
Upvote 0
I'll post something later as I am with some Pompey supporters celebrating (if you aren't in the UK you won't understand).
 
Upvote 0
if I wanted it to begin from B4 (the rows above are headings etc. and A is just blank which don't need copying)
Again, sheet 2 would be the exact copy

thanks!

OK, now I am reading this on a computer it reads a bit different to what I originally thought so just to be clear a few questions...

First of all can you confirm that you want the rows being copied from Sheet1 to be restricted to Row 4 onwards (I assume that sometimes you are typing "completed" in the cells above otherwise the code wouldn't copy anyway)?

Can you post what column is the last column with data to copy in Sheet1?

Just to be clear you want the data to be "pasted" to Sheet2 starting in column B?
 
Upvote 0
OK, now I am reading this on a computer it reads a bit different to what I originally thought so just to be clear a few questions...

First of all can you confirm that you want the rows being copied from Sheet1 to be restricted to Row 4 onwards (I assume that sometimes you are typing "completed" in the cells above otherwise the code wouldn't copy anyway)?

Can you post what column is the last column with data to copy in Sheet1?

Just to be clear you want the data to be "pasted" to Sheet2 starting in column B?

Sorry for the delay in response

Ok so yes, I wanted the 'pasting' to take effect from B4 in Sheet 1 (Column A is blank) (The top 3 rows are headings which I obviously don't want transferring over)
So B4 - N4 is going to be the row of data that needs transferring, with M4 being the 'completed' column which would be the 13th column as I think you did the code by number
and yes
I wanted pasted info on Sheet 2 to start from B4 again, so it sits in B4 - N4

Make sense? :)
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top