cut & paste between sheets based on cell data
If you want the event macro to work across all sheets, use the
Workbook_SheetChange() event macro.
You don't say what values in column A, nor how they're inputted
(direct entry? validation dropdown?). I'll assume that it's a direct
entry that triggers the _SheetChange event. Adapt to suit:
Private Sub Workbook_SheetChange( _
ByVal Sh As Object, ByVal Target As Excel.Range)
Dim sShtName As String
Dim sAddr As String
With Target
If .Count 1 Then Exit Sub
If .Column = 1 Then
Select Case UCase(Left(.Value, 1))
Case "O"
sShtName = "Open"
Case "P"
sShtName = "Pending"
Case "T"
sShtName = "Transfers"
Case "H"
sShtName = "Hire"
Case "C"
sShtName = "Complete"
Case Else
MsgBox "Enter (O)pen, (P)ending, " & _
"T(ransfers), (H)ire, or (C)omplete"
End Select
If sShtName < "" Then
Application.EnableEvents = False
sAddr = .Address
.EntireRow.Cut Sheets(sShtName).Range( _
"A" & Rows.Count).End(xlUp).Offset(1, 0)
Sh.Range(sAddr).EntireRow.Delete
Application.EnableEvents = True
End If
End If
End With
End Sub
In article ,
"Mike Reisinger" wrote:
I know I have seen this one posted before, but I can not find it.
I have a workbook with 5 sheets - OPEN, PENDING, TRANSFERS, HIRE, COMPLETE.
This is also the status of our open positions which is column A.
Depending on what Column A is on any of the sheets, I would like the row to
go to the bottom of the proper sheet (and leave no blank row). I believe
this is a workbook event change function, but cannot seem to figure it out.
Help.
|