PDA

View Full Version : Solved: Move rows to second worksheet based on value



Randomlight
08-21-2007, 01:59 AM
What ho guys, forgive me if this has appeared before, had a search and couldn?t find anything. I got a workbook for tracking payments for parts returned back to main warehouse and the reasons for non credit/over credit etc and would like to move an entire row of data to a second sheet if we received the correct credit. I have a column where the expected amount is taken from the recieved amount and where this equals zero I would like the entire row to move to sheet 2 I?ve already come up with this from a good deal of interweb searching but for some reason just doesn?t work, Anyone have any idea what might be up....

Sub Moveworksheets()
Application.ScreenUpdating = False
Dim myrange As Range
For Each myrange In Range("m2", Range("m2").End(xlDown))
If myrange = "0,00" Or myrange = "0" Then
Rows(myrange.Row).EntireRow.Copy
Sheets("Correct").Select
'This is generally where it all goes wrong and the weeping begins
Sheets("Correct").Range("A1").End(xlDown).Offset(1, 0).PasteSpecial xlPasteAll
Application.CutCopyMode = False
Sheets("Incorrect").Select
Rows(myrange.Row).EntireRow.Delete
End If
Next myrange
Application.ScreenUpdating = True
End Sub

I am (as may be obvious) fairly ignorant of VBA so please be gentle : pray2:

And ta in advance

Bob Phillips
08-21-2007, 02:19 AM
Sub Moveworksheets()
Application.ScreenUpdating = False
Dim myrange As Range
Dim LastRow As Long
Dim rng As Range
For Each myrange In Range("m2", Range("m2").End(xlDown))
If myrange = "0,00" Or myrange = "0" Then
myrange.EntireRow.Copy
'This is generally where it all goes wrong and the weeping begins
With Sheets("Correct")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
If LastRow > 1 Or .Range("A1").Value <> "" Then
LastRow = LastRow + 1
End If
.Range("a" & LastRow).PasteSpecial xlPasteAll
End With
If rng Is Nothing Then
Set rng = myrange
Else
Set rng = Union(rng, myrange)
End If
End If
Next myrange
If Not rng Is Nothing Then rng.EntireRow.Delete
Application.ScreenUpdating = True
End Sub

Randomlight
08-21-2007, 02:58 AM
Massive thanks that man, for the help and the speedy reply

I owe you a drink :D

:bow: