jason_kelly
12-03-2010, 06:17 AM
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
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