I have a workbook with two worksheets containing about 20 columns and 2800
rows of data. One column lists the assets as "active" or "inactive". One
worksheet is for the "active" assets and one worksheet for "inactive". I am
new to VBA and would like to know, and if possible someone supply a program
that can move the row of data from one sheet to the other when that one item
is changed. Thanks!

I will assume that Col. B contains the words "active" & "inactive". Sheet(1)
contains "active" rows and Sheet(2) contains "inactive" rows. Because you
want the code to execute when the cell text is changed to "active" or
"inactive" for each sheet you can take advantage of the Worksheet_Change
Event for each sheet.

Paste this in the Sheet1 module:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim InputRow As Long
Dim TargetRow As Long

'runs code if a cell is changed in Col.B
If Target.Column = 2 Then

'last row in on the used range in InActive Sheet
InputRow = Sheets(2).UsedRange.Rows.count

'moves rows with "inactive" to InActive Sheet
If LCase(Target.Text) = "inactive" Then
TargetRow = Target.Row
Target.EntireRow.Cut
Destination:=Sheets(2).Cells(InputRow + 1, 1)
Sheets(1).Rows(TargetRow).Delete
End If
End If

End Sub

and paste this in the Sheet(2) module:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim InputRow As Long
Dim TargetRow As Long

'runs code if a cell is changed in Col.B
If Target.Column = 2 Then

'last row in on the used range in Active Sheet
InputRow = Sheets(1).UsedRange.Rows.count

'moves rows with "active" to Active Sheet
If LCase(Target.Text) = "active" Then
TargetRow = Target.Row
Target.EntireRow.Cut
Destination:=Sheets(1).Cells(InputRow + 1, 1)
Sheets(2).Rows(TargetRow).Delete
End If
End If

End Sub

Note: this code will only avtive if the cell you are changing is in Col.B.

I hope this helps! If so, please click "Yes" below.
--
Cheers,
Ryan


"thecdnmole" wrote:

I have a workbook with two worksheets containing about 20 columns and 2800
rows of data. One column lists the assets as "active" or "inactive". One
worksheet is for the "active" assets and one worksheet for "inactive". I am
new to VBA and would like to know, and if possible someone supply a program
that can move the row of data from one sheet to the other when that one item
is changed. Thanks!

Thanks, but the column is actually "F", so I was trying to see in the script
where to change b to f, and actually there are more words than those two, but
I can probably use = and < active. It only needs to update when the
workbook is opened, not when the cell is changed and IF possible, sorted
alphabetically by column A. If I can figure out what to change I will give
this a try, otherwise I may need more help!

"RyanH" wrote:

I will assume that Col. B contains the words "active" & "inactive". Sheet(1)
contains "active" rows and Sheet(2) contains "inactive" rows. Because you
want the code to execute when the cell text is changed to "active" or
"inactive" for each sheet you can take advantage of the Worksheet_Change
Event for each sheet.

Paste this in the Sheet1 module:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim InputRow As Long
Dim TargetRow As Long

'runs code if a cell is changed in Col.B
If Target.Column = 2 Then

'last row in on the used range in InActive Sheet
InputRow = Sheets(2).UsedRange.Rows.count

'moves rows with "inactive" to InActive Sheet
If LCase(Target.Text) = "inactive" Then
TargetRow = Target.Row
Target.EntireRow.Cut
Destination:=Sheets(2).Cells(InputRow + 1, 1)
Sheets(1).Rows(TargetRow).Delete
End If
End If

End Sub

and paste this in the Sheet(2) module:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim InputRow As Long
Dim TargetRow As Long

'runs code if a cell is changed in Col.B
If Target.Column = 2 Then

'last row in on the used range in Active Sheet
InputRow = Sheets(1).UsedRange.Rows.count

'moves rows with "active" to Active Sheet
If LCase(Target.Text) = "active" Then
TargetRow = Target.Row
Target.EntireRow.Cut
Destination:=Sheets(1).Cells(InputRow + 1, 1)
Sheets(2).Rows(TargetRow).Delete
End If
End If

End Sub

Note: this code will only avtive if the cell you are changing is in Col.B.

I hope this helps! If so, please click "Yes" below.
--
Cheers,
Ryan


"thecdnmole" wrote:

I have a workbook with two worksheets containing about 20 columns and 2800
rows of data. One column lists the assets as "active" or "inactive". One
worksheet is for the "active" assets and one worksheet for "inactive". I am
new to VBA and would like to know, and if possible someone supply a program
that can move the row of data from one sheet to the other when that one item
is changed. Thanks!