Moving Data to Another Worksheet

[jason_kelly]

Hi There,

I need your help,

For some reason, I cant get the code below to work to move the data over to another worksheet "Inactive_Data" and delete it from the existing worksheet when a textbox in form contains the value: "Inactive".

Any ideas?

Ps. I have also attached the spreadsheet for ease of reference.

[vba]
'=============================================================
'MODIFY FUNCTION [+/-]
'=============================================================
Private Sub V3_MOD()
Dim rFind As Range, ws As Worksheet
For Each ws In Sheets(Array("Active_Data", "Inactive_Data"))
Set rFind = ws.Columns.Find(What:=Me.h3, LookAt:=xlWhole)
If Not rFind Is Nothing Then
MsgBox rFind.Offset(, 8)

rFind.Offset(, -2) = Me.h1
rFind.Offset(, -1) = Me.h2
rFind.Offset(, 0) = Me.h3
rFind.Offset(, 1) = Me.h4
rFind.Offset(, 2) = Me.h5
rFind.Offset(, 3) = Me.h6
rFind.Offset(, 6) = Me.h7
rFind.Offset(, 7) = Me.h8
rFind.Offset(, 9) = Me.h10
rFind.Offset(, 10) = Me.h11
rFind.Offset(, 11) = Me.h12
rFind.Offset(, 12) = Me.h13

If notify.Value = True Then
rFind.Offset(, 4) = "Yes"
Else
rFind.Offset(, 4) = "No"
End If

If remind.Value = True Then
rFind.Offset(, 5) = "Yes"
Else
rFind.Offset(, 5) = "No"
End If
rFind.Offset(, 8) = Me.h9

If rFind.Offset(, 8) = "Inactive" Then
ws.Range("A" & CurRow).Cut Destination:=Sheets("Inactive_Data").Range("A" & Rows.Count).End(xlUp).Offset(1)
ws.Range("A" & CurRow).EntireRow.Delete xlUp
End If

Exit Sub
End If
Next ws
End Sub
[/vba]

Much thanks and appreciation for all your help.

Cheers,

Jay

[Zack Barresse]

Hi,

Why are you looping through both sheets? I may not be understanding your data set, but wouldn't you just want to do it on the Active Data sheet?

[jason_kelly]

Hi Zack,

Im using the code above to dually do 2 things:

1.) modify the data on the active and inactive sheets and to also transfer a line of data from the active to the inactive sheet.

2.) Transfer a line of data from active to inactive and vise versa.