Solved: Modifying Data in a Worksheet from a Userform

[jason_kelly]

Is there a way to amend the code below:

Private Sub cmdModify_Click()

Dim rFind As Range, ws As Worksheet

For Each ws In Sheets(Array("Type1", "Type2"))
Set rFind = ws.Columns.Find(What:=Me.txt1, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
rFind.Offset(, 1) = Me.txt2
rFind.Offset(, 2) = Me.txt3
rFind.Offset(, 3) = Me.txt4
rFind.Offset(, 4) = Me.txt5
Exit Sub
End If
Next ws

End Sub


1.) Such that when Asset type is modified from Type 1 to Type 2, the data will be copied over to the Type2 Worksheet (on the next blank empty row) and the old data from the Type1 worksheet will be removed.

2.) Such that when Asset type is modified from Type 2 to Type 1, the data will be copied over to the Type1 worksheet (on the next blank empty row) and the old data from the Type2 worksheet will be removed.

Any help with this is greatly appreciated.

Ps. I have attached my Excel doc with my userform as an example

Much thanks in advance,

Jay

[Tinbendr]

Try this
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.txt1, LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
If Not rFind Is Nothing Then
Select Case Ws.Name
Case "Active_Data"
CurRow = rFind.Row
With Ws
LastRow = Worksheets("Inactive_Data").Cells.Find("*", searchorder:=xlRows, searchdirection:=xlPrevious).Row
.Range("A" & CurRow).EntireRow.Cut Worksheets("Inactive_Data").Range("A" & LastRow + 1)
.Range("A" & CurRow).EntireRow.Delete xlUp
End With

Case "Inactive_Data"
CurRow = rFind.Row
With Ws
LastRow = Worksheets("Active_Data").Cells.Find("*", searchorder:=xlRows, searchdirection:=xlPrevious).Row
.Range("A" & CurRow).EntireRow.Cut Worksheets("Active_Data").Range("A" & LastRow + 1)
.Range("A" & CurRow).EntireRow.Delete xlUp
End With

End Select

Exit Sub
End If
Next Ws
End Sub

[jason_kelly]

Thanks very much for the awesome work and help David. I can't thank you enough!

I did mistakenly neglect to say that I am using Excel 2000 as per your comments.

Ok so heres the scoop now. It works great except, i'd like to (if possible) put in an if condition to stop it from moving over the data on one condition:

If the record is already in Active_Data worksheet and I haven't changed the Asset Category to "Inactive_Data" and vice versa, just to re-write the data back on its current worksheet if i've updated and changed any info on the userform.

Other than that, It works great. Thanks again for time.

I appologize for being such a demanding newbie.

Jay,

Ps. I've re-uploaded the spreadsheet with the new code that you hav provided me.

[jason_kelly]

Here is the attachment

[Tinbendr]

If you have a conditional move, then I would seperate the Modify button into a Modify and Move button.

[Tinbendr]

Try this.

[jason_kelly]

Thanks very much for the awesome help Tinbendr.

I think that would work out very well.

[Simon Lloyd]

If your query has been solved please take a moment to let us and eveyone else know by either going to, Thread Tools > "Mark This Thread As Solved"