Cut and Paste macro based on criteria then delete empty rows

[samst]

Hi All,
First time poster and VBA bewbie. Any help would be great!!
I'm using ecxel 2000
Here's my problem:

I have a worksheet (Sheet1) from which I need to cut paste any row (in
the Range A3:AD300) where column "O" has a certain value in it into
Sheet2. I'd like the pasting to begin in the first blank row of
Sheet2. Then I'd like the blank rows of Sheet1 to be deleted. Is this
possible?


Many thanks

 

[Bob Phillips]

This should do what you want. Paste it into a general code module

Sub CutData()
Dim oWS1 As Worksheet
Dim oWS2 As Worksheet
Dim cRowLast As Long
Dim i As Long

Application.ScreenUpdating = False

Set oWS1 = Worksheets("Sheet1")
Set oWS2 = Worksheets("Sheet2")
cRowLast = oWS2.Cells(Rows.Count, "A").End(xlUp).Row
If cRowLast <> 1 Or oWS2.Range("A1") <> "" Then
cRowLast = cRowLast + 1
End If

For i = 300 To 3 Step -1
If oWS1.Cells(i, "O") = "test" Then
oWS1.Range("A" & i).EntireRow.Cut Destination:=oWS2.Range("A" &
cRowLast)
cRowLast = cRowLast + 1
oWS1.Range("A" & i).EntireRow.Delete
End If
Next i

Application.ScreenUpdating = True

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Don Guillett]

what about something like to move instead of cut/paste/delete
for each c in [o3300]
lastrow=sheets("sheet1").range("a65536").end(xlup).row+1
if c="Joe" then c.entirerow cut sheets("sheet1").range("a2:a"&lastrow)
next

 

[s ss]

Hi everyone
Thanks for your help but I think I'm doing something wrong

I copied your macro into Module 1 and put the word "test" in cell "o4"
and ran the macro but nothing happened. Any troubleshooting ideas?

thanks again


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Mail me your workbook direct and I will look at it.

Bob