Automatically move Row to another sheet

RattlingCarp3048

Board Regular
Joined
Jan 12, 2022
Messages
166
Office Version
  1. 365
Platform
  1. Windows
You all have been so helpful in learning VBA so I am back again!

I need a code that basically says if Column T >/= 10 on Sheet 1, move the entire row to next available row on Sheet 2.

Headers are in Row 1 and are exactly the same on both sheets.

Value in Column T is formula based (=IF(S2-R2<0,"",S2-R2)).

I tried to record myself but couldn't quite figure out how to manipulate it to move the entire row.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Do you want this to run on a complete set of already entered in data (so there may be multiple rows to move at once),
or do you want this to run as data is being entered (so only one row would be moved at a time)?

If you want something that runs automatically as data is being entered, how do we know when the data entry is complete?
Do they always fill in the same cell last?
 
Upvote 0
Do you want this to run on a complete set of already entered in data (so there may be multiple rows to move at once),
or do you want this to run as data is being entered (so only one row would be moved at a time)?

If you want something that runs automatically as data is being entered, how do we know when the data entry is complete?
Do they always fill in the same cell last?
I have a macro currently in place that compiles data to generate a report. Once the report is generated (only once every day) I want the above to happen. The purpose is that anything on sheet 2 will need to be further researched by the staff and can be manually cleared once the research is completed.
 
Upvote 0
Have you thought of just using Data Filters, to only show records where the value in column T is greater than 10?
With Advanced Data Filters, you can even filter the results to a new location (sheet).
 
Upvote 0
Have you thought of just using Data Filters, to only show records where the value in column T is greater than 10?
With Advanced Data Filters, you can even filter the results to a new location (sheet).
That only copies the row. I want to completely move the row
 
Upvote 0
Approximately how many rows of data are we talking about? I ask because reasons are not efficient, especially if there are a lot of rows, but if we are talking about a small number, it would probably be all right.

Also, does it matter what order the data is put in on the other sheet? I ask because when looping through rows to delete them, you need to work in reverse order, which means the rows on Sheet2 would be in the reverse order they were originally found in.
 
Upvote 0
Approximately how many rows of data are we talking about? I ask because reasons are not efficient, especially if there are a lot of rows, but if we are talking about a small number, it would probably be all right.

Also, does it matter what order the data is put in on the other sheet? I ask because when looping through rows to delete them, you need to work in reverse order, which means the rows on Sheet2 would be in the reverse order they were originally found in.
The initial run would be about 300-ish rows but going forward after that should be less that 10 on a daily basis.

The order in which they are placed onto sheet 2 does not matter. That can be filtered/sorted by the end user as needed.
 
Upvote 0
OK, try this:
VBA Code:
Sub MyMoveRows()

    Dim lr As Long
    Dim r As Long
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Application.ScreenUpdating = False
    
'   Assign worksheets to worksheet variables
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    
'   Find last row with data in column T on sheet 1
    lr = ws1.Cells(Rows.Count, "T").End(xlUp).Row
    
'   Loop through all rows on sheet 1 backwards, up to row 2
    For r = lr To 2 Step -1
'       See if value in column T >= 10
        If ws1.Cells(r, "T").Value >= 10 Then
'           Copy to bottom of sheet 2
            ws1.Rows(r).Copy ws2.Cells(Rows.Count, "T").End(xlUp).Offset(1, -19)
'           Delete row on sheet 2
            ws1.Rows(r).Delete Shift:=xlUp
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Solution
runt time error 13 type mismatch.

direct copy/paste then updated the sheet names.

1642195841062.png
 
Upvote 0
Add this line just above that line in yellow, and try again and tell me what it returns:
VBA Code:
MsgBox ws1.Name
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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