VB Code to move data from one sheet to another

Matt

Board Regular
Joined
Feb 16, 2002
Messages
212
Please can someone help me with the following scenario. I want to look through column B in Sheet 1 and where the word "complete" is present in column B, I want to remove the entire row and paste it into sheet 2, starting at the first blank row. Any suggestions for the required code would be greatly appreciated.

Thanks

Matt
This message was edited by Matt on 2002-04-16 11:23
This message was edited by Matt on 2002-04-16 11:38
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi Matt,

This code worked for me. Let us know if it works for you.

---begin VBA---
Sub tester()
Dim rng As Range, usedcell As Range
Dim lastrow As Long, x As Boolean

Set rng = Intersect(Sheets("Sheet1").UsedRange, _
Sheets("Sheet1").Range("B:B"))

For Each usedcell In rng
x = Evaluate("=NOT(ISERROR(SEARCH(""COMPLETE""," & usedcell.Address & ",1)))")
If x Then
lastrow = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Sheet1").Rows(usedcell.Row).Copy Sheets("Sheet2").Rows(lastrow + 1)
End If
Next usedcell

End Sub
---end VBA---

HTH,
Jay

EDIT: If you want to cut the data rather than copy it to the new sheet, change

Sheets("Sheet1").Rows(usedcell.Row).Copy Sheets("Sheet2").Rows(lastrow + 1)

to

Sheets("Sheet1").Rows(usedcell.Row).Cut Sheets("Sheet2").Rows(lastrow + 1)
This message was edited by Jay Petrulis on 2002-04-16 12:13
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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