Modifying Data on Another Worksheet

[jason_kelly]

Hi there,

I need your help.

I am trying to modify the code below in the attached excel doc, to make the "Add/Update" button on my userform to also make changes to the listed items in the "Inactive_Data" worksheet and not just the "Active_Data" worksheet like the code below was orginally intended to do.

(Using Excel 2000 VBA)

Sub test()
With Sheets("Active_Data")
'check if item is already in database
On Error Resume Next
Rw = .Range("C:C").Find(txt3.Value, LookIn:=xlValues, LookAt:=xlPart).Row

On Error Resume Next

'find first empty row in database
If Rw = 0 Then Rw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

'copy the data to the database
.Cells(Rw, 1).Value = UCase(Me.txt1.Value)
.Cells(Rw, 2).Value = UCase(Me.txt2.Value)
.Cells(Rw, 3).Value = UCase(fn)
.Cells(Rw, 4).Value = UCase(Me.txt4.Value)
.Cells(Rw, 5).Value = Me.txt5.Value
.Cells(Rw, 6).Value = UCase(Me.txt6.Value)

.Cells(Rw, 9).Value = Me.txt7.Value
.Cells(Rw, 10).Value = UCase(Me.txt8.Value)
.Cells(Rw, 11).Value = UCase(Me.txt9.Value)
.Cells(Rw, 12).Value = UCase(Me.txt10.Value)
.Cells(Rw, 13).Value = UCase(Me.txt11.Value)
.Cells(Rw, 14).Value = UCase(Me.txt12.Value)
.Cells(Rw, 15).Value = UCase(Me.txt13.Value)

If notify.Value = "True" Then
.Cells(Rw, 7).Value = "Yes"
Else
.Cells(Rw, 7).Value = "No"
End If

If remind.Value = "True" Then
.Cells(Rw, 8).Value = "Yes"
Else
.Cells(Rw, 8).Value = "No"
End If

End With

The following code below will also need to be modified. I am really clueless as to how to modify it below, to only move the line of data from the "Active_Data" to "Inactive_Data" only if its listed in the "Active_Data" worksheet, otherwise, just make changes to the record in the "Inactive_Data" worksheet.

If txt9.Value = "CLOSED" Or txt9.Value = "DEFERRED" Then
Dim LR As Long, i As Long
With Sheets(ws)
LR = .Range("K" & .Rows.Count).End(xlUp).Row
For i = 1 To LR
If .Range("K" & i).Value = "CLOSED" Then .Rows(i).Cut Destination:=Sheets("Inactive_Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
If .Range("K" & i).Value = "DEFERRED" Then .Rows(i).Cut Destination:=Sheets("Inactive_Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
On Error Resume Next
.Range("K1:K" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End With
End If

End Sub

For Ease of Reference, I have attached the Excel Document.

Any help is greatly appreciated on this, before I pull my entire hair out trying to figure this one out.

Thanks for all your help and support.

Jay