cut & paste between sheets based on cell data

  • Thread starter Thread starter Mike Reisinger
  • Start date Start date
M

Mike Reisinger

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.
 
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
 
JE - sorry to leave out some key info. The row data is typically inputted
in the OPEN sheet. Column A (status) is a validation list. As the status
changes to HIRED, TRANSFER, etc, I would like the entire row to be cut and
pasted into the appropriate sheet. I would like it to be able to move
around between all sheets depending on the Column A (status).

Also, where exactly to I paste this?

Thanks!
 
Hi Mike,

Thank you for posting in MSDN managed newsgroup!

You may need to define one variable which will store the last position for your record in each sheet, for example iSheet2Lowest which stores the
lowest value for the columnA in worksheet2. I write the sample codes for you in the worksheet change event function:

'Code begin-------------------

'first, check whether the change is raised by the cell you specified which contains the state: open, pending ...

'Then use the code below to copy the cell content to another sheet based on the scenario
'Note please substitue the sheet name according your scenario

Sheets("Sheet1").Select
Range("A9").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select

Dim strPos
strPos = "A" & iSheet2Lowest

Range(strPos).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'Please note, after the paste, update the value of lowest varaiable
iSheet2Lowest = iSheet2Lowest + 1

'Code end---------------------

Please feel free to let me know if you have any further questions.

Does this answer your question? Thank you for using Microsoft NewsGroup!

Wei-Dong Xu
Microsoft Product Support Services
Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
One way:

If it's only to operate in the sheet named OPEN, put this in the
sheet's code module (right-click on the sheet's tab and select View
Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim sAddr As String
With Target
If .Count > 1 Then Exit Sub
sAddr = .Address
Application.EnableEvents = False
.EntireRow.Cut Sheets(.Value).Range( _
"A" & Rows.Count).End(xlUp).Offset(1, 0)
Range(sAddr).EntireRow.Delete
Application.EnableEvents = True
End With
End Sub
 
Back
Top