Cut & Paste rows to 2 different worksheets

[pantherjad]

I am the "tech" guy for my small sales company. However, I am VERY new to VBA.

We have a workbook we use to track sales prospects. We need to cut/paste rows (each sales prospect/lead) to a particular worksheet once it has been classified as either "Inked" (signed/closed) or "Dropped." This classification is done via a drop down menu in the 3rd column of our “PIPE” worksheet. We already have additional sheets labeled as "DROP" and "INKED" respectively.

Below is my VBA code thus far. It copies the rows exactly as needed. I just need the rows to be cut/paste instead (moved).

Additionally, is it possible to create a macro that will run automatically as soon as a given row has been classified as dropped or inked via the drop down menu in the 3rd row? Or must I run the macro in order to move the rows?


Sub UpdateStage()
Application.ScreenUpdating = False
Source = ActiveSheet.Name
a = ActiveSheet.UsedRange.Rows.Count
Sheets(Source).Activate
If Sheets(Source).AutoFilterMode = False Then
Range("a1:z" & a).AutoFilter
End If
Selection.AutoFilter Field:=3, Criteria1:="0. Drop"
Selection.SpecialCells(xlCellTypeVisible).Copy
Sheets("DROP").Range("a1").PasteSpecial
Sheets(Source).Activate
Selection.AutoFilter Field:=3, Criteria1:="7. Inked"
Selection.SpecialCells(xlCellTypeVisible).Copy
Sheets("INKED").Range("a1").PasteSpecial
Sheets(Source).ShowAllData
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub


Function wsExist(nm As String) As Boolean
wsExist = False

For Each ws In Worksheets
If ws.Name = nm Then
wsExist = True
Exit Function
End If
Next ws
End Function


any help would be greatly appreciated.

Also, I did read through the move rows thread from Nov 08, it's just that I didn't find it helpful enough to solve my particular problem.

 

[PHV]

Use the Cut method instead of Copy ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pantherjad]

As in:

Selection.SpecialCells(xlCellTypeVisible).Cut

vs.

Selection.SpecialCells(xlCellTypeVisible).Copy

?

When I tried that it wouldn't work. So I don't know if there is something else I am missing.