Cut and Paste to A Seperate Worksheet
 

Hello,

I would like to have VBA look through "worksheet A". If a cell in
"worksheet A" meets a condition then copy, cut, and paste that row to
"worksheet b".

The hard part... is VBA has to somehow know that if cells a3-a10 are
full then the new information has to be put in cell a11. Please help
me by writing new code that addresses my problem or correct the flawed
code below. Thank you.



My current code is:

ThisWorkbook.Sheets("worksheet a").Range("v3").Activate

Do While IsEmpty(ActiveCell.Offset(0, -21)) = False
If ActiveCell.Value = "x" Then ActiveCell.EntireRow.Cut
(Sheets("Completed Jobs 2004").Range("a4000"))
If IsEmpty(ActiveCell) Then ActiveCell.EntireRow.Delete


' this part of the loop does not work
On Error Resume Next
ThisWorkbook.Sheets("Completed Jobs 2004").Range("a3:a4000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete
ActiveSheet.UsedRange
'Ends the part of the loop that doesn't work

ActiveCell.Offset(1, 0).Select
Loop


End Sub


---
Message posted from http://www.ExcelForum.com/

Cut and Paste to A Seperate Worksheet
 

Have a look in vba HELP index for FIND. There is a good example.

--
Don Guillett
SalesAid Software

"Excel Nerd " wrote in message
...
Hello,

I would like to have VBA look through "worksheet A". If a cell in
"worksheet A" meets a condition then copy, cut, and paste that row to
"worksheet b".

The hard part... is VBA has to somehow know that if cells a3-a10 are
full then the new information has to be put in cell a11. Please help
me by writing new code that addresses my problem or correct the flawed
code below. Thank you.



My current code is:

ThisWorkbook.Sheets("worksheet a").Range("v3").Activate

Do While IsEmpty(ActiveCell.Offset(0, -21)) = False
If ActiveCell.Value = "x" Then ActiveCell.EntireRow.Cut
(Sheets("Completed Jobs 2004").Range("a4000"))
If IsEmpty(ActiveCell) Then ActiveCell.EntireRow.Delete


' this part of the loop does not work
On Error Resume Next
ThisWorkbook.Sheets("Completed Jobs 2004").Range("a3:a4000").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete
ActiveSheet.UsedRange
'Ends the part of the loop that doesn't work

ActiveCell.Offset(1, 0).Select
Loop


End Sub


---
Message posted from http://www.ExcelForum.com/