Hi Jw

Perhaps should have explained that there are 26 sheets in the Workbook each
one named and selected by a drop down Combo Box that is why I used
ActiveSheet. Therefore if I select the " J.Bloggs " sheet and add the info
into the "Result" sheet I then want the macro after inserting the info in the
"Result" Sheet to return to the "J.Bloggs" Sheet which was the original
ActiveSheet.

Hope that explains it better what I want to do.
--
Many thanks

hazel


"JW" wrote:

Don't exactly follow what you are asking. If you just want to return
to the original sheet that you started the macro on, you can set it to
a variable at the top of your code and then just activate that sheet
whenever you need to within your code. Something like:
Dim startSheet as Worksheet
Set startSheet = ActiveSheet

Then use something like below to return to the startSheet
startSheet.Activate

Be sure to set the variable to nothing at the end of the sub
startSheet = Nothing

Or, you could use the sheet name instead
Dim startSheet a s String
startSheet = ActiveSheet.Name

Then use something like below to return to the startSheet
Worksheets(startSheet).Activate

HTH

Hazel wrote:
Hi All

Had some fantastic help from Tom yesterday and it worked perfectly no
problem - however today when I added to the "Result Sheet" I had to close
down the UserForm and start again with the ActiveSheet I was using to search
again for more info.
Below is the code from yesterday followed by Adding the info found to the
"Result Sheet" how do I get back to my previous Active Sheet.

Private Sub Cmd100_Click()
Dim rngToSearch As Range
Dim rngFound As Range
Set rngToSearch = ActiveSheet.Columns("F")
Set rngFound = rngToSearch.Find(What:=Tb100.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)
If rngFound Is Nothing Then
MsgBox "Sorry " & Tb100.Value & " was not found."
Else
With Lb1
For i = 0 To .ListCount - 1
If .List(i, 5) = Tb100.Value Then
Lb1.ListIndex = i
For Each cell In Range(Cells(rngFound.Row, 7), _
Cells(rngFound.Row, 7))
s = cell.Value & ","
Next
Tb101.Value = Left(s, Len(s) - 1)
Exit For
End If
Next
rngFound.Select
End With
End If


End Sub

Private Sub Cmd2_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Result")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 5) _
.End(xlUp).Offset(1, 0).Row



'copy the data to the database
ws.Cells(iRow, 2).Value = Me.Tb25.Value
ws.Cells(iRow, 3).Value = Me.Tb17.Value
ws.Cells(iRow, 4).Value = Me.Tb18.Value
ws.Cells(iRow, 5).Value = Me.Tb14.Value
ws.Cells(iRow, 6).Value = Me.Tb15.Value
ws.Cells(iRow, 7).Value = Me.Tb16.Value
ws.Cells(iRow, 8).Value = Lb1.List(, 4)
ws.Cells(iRow, 9).Value = Me.Tb2.Text
ws.Cells(iRow, 10).Value = Me.Tb19.Value

'Call Module1.Macro4


'clear the data
Me.Tb2.Text = ""
Me.Tb11.Value = ""
Me.Tb12.Value = ""
Me.Tb13.Value = ""
Me.Tb14.Value = ""
Me.Tb15.Value = ""
Me.Tb16.Value = ""
Me.Tb19.Value = ""
Me.Tb26.Value = ""
Me.Tb27.Value = ""
Me.Tb100.Value = ""



End Sub


--
Many thanks

hazel