Automatically move Row to another sheet

RattlingCarp3048

Board Regular
Joined
Jan 12, 2022
Messages
141
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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,124
Office Version
  1. 365
Platform
  1. Windows
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?
 

RattlingCarp3048

Board Regular
Joined
Jan 12, 2022
Messages
141
Office Version
  1. 365
Platform
  1. Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,124
Office Version
  1. 365
Platform
  1. Windows
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).
 

RattlingCarp3048

Board Regular
Joined
Jan 12, 2022
Messages
141
Office Version
  1. 365
Platform
  1. Windows
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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,124
Office Version
  1. 365
Platform
  1. Windows
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.
 

RattlingCarp3048

Board Regular
Joined
Jan 12, 2022
Messages
141
Office Version
  1. 365
Platform
  1. Windows
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,124
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

RattlingCarp3048

Board Regular
Joined
Jan 12, 2022
Messages
141
Office Version
  1. 365
Platform
  1. Windows
runt time error 13 type mismatch.

direct copy/paste then updated the sheet names.

1642195841062.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
66,124
Office Version
  1. 365
Platform
  1. Windows
Add this line just above that line in yellow, and try again and tell me what it returns:
VBA Code:
MsgBox ws1.Name
 

Forum statistics

Threads
1,181,607
Messages
5,930,881
Members
436,764
Latest member
avalladarez

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
Top