Moving data between worksheets

  • Thread starter Thread starter lapierce66
  • Start date Start date
L

lapierce66

I'm creating a spreadsheet to track various issues for myself and
another coworker and we've created 2 worksheets for this. One holds
all ACTIVE issues and one holds all of the CLOSED issues. What I would
like to do is have Excel move an entire row of data from the ACTIVE
worksheet to the CLOSED worksheet based on the value of a particular
field. The end result being that only open/active issues are shown on
the ACTIVE worksheet and only closed issues show up in CLOSED.

Does anyone know how to do this or if it's even possible?
Any help would be greatly appreciated.

Thanks,
-- LP
 
LP,

You can do this using the workbook's sheetchange event, example code
given below. For this to work, you need a sheet named ACTIVE and a
sheet named CLOSED, and in each of those sheets column 1 has values of
only ACTIVE or CLOSED. When ACTIVE is changed to CLOSED, the entire
row will be moved to the CLOSED sheet, at the bottom of the data set,
and vice versa. The code will only work if you restrict your entries
in column 1 to valid sheet names.

The code should be put into the ThisWorkbook object's codemodule.
See "Programming Event Procedures In VBA" at Chip Pearson's website:

http://www.cpearson.com/excel/events.htm

HTH,
Bernie

Private Sub Workbook_SheetChange( _
ByVal Sh As Object, ByVal Target As Range)
Dim myName As String
If Target.Cells.Count <> 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
If Target.Value <> Sh.Name Then
On Error GoTo InValidName
myName = Worksheets(Target.Value).Name
Application.EnableEvents = False
Target.EntireRow.Copy _
Worksheets(Target.Value).Range("A65536").End(xlUp)(2)
Target.EntireRow.Delete
Application.EnableEvents = True
End If

Exit Sub

InValidName:
MsgBox "Only enter valid worksheet names in this column"
Application.Undo
End Sub
 
Back
Top