Fiind from textbox and paste into sheet1
 

Here is the problem. I am not able to get the info to paste into sheet1. I
can use the find function to find the cell but not able to paste. I would
also like to mention that I have multiple text boxes to search from. If
anyone can help i would greatly appreciate it. Thanks
-mark
Private Sub cmdFind_Click()
Dim R As Range
Dim Sheet As Worksheet
Sheets("sheet1").Select
Sheets("Sheet2").Select
Cells.Find(What:=txtFind.Text, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

Fiind from textbox and paste into sheet1
 

Here is some code that should work for you. I did not know where you wnated
the item pasted (paste regular or paste special for that matter) so I pasted
regular in the first blank cell in column a on the destination sheet. It is
important when doing a find to handle the case when it is not found. I just
did a message back to the user...

Private Sub cmdFind_Click()
Dim wksCopyFrom As Worksheet
Dim wksCopyTo As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngCopyTo As Range

Set wksCopyFrom = Sheets("Sheet1")
Set rngToSearch = wksCopyFrom.Cells
Set rngFound = rngToSearch.Find(txtFind.Text, , xlValues, xlWhole)
If rngFound Is Nothing Then
MsgBox "That item was not found."
Else
Set wksCopyTo = Sheets("Sheet2")
Set rngCopyTo = wksCopyTo.Cells(Rows.Count, "A").End(xlUp).Offset(1,
0)
rngFound.Copy rngCopyTo
End If

End Sub
--
HTH...

Jim Thomlinson


"Mark Cover" wrote:

Here is the problem. I am not able to get the info to paste into sheet1. I
can use the find function to find the cell but not able to paste. I would
also like to mention that I have multiple text boxes to search from. If
anyone can help i would greatly appreciate it. Thanks
-mark
Private Sub cmdFind_Click()
Dim R As Range
Dim Sheet As Worksheet
Sheets("sheet1").Select
Sheets("Sheet2").Select
Cells.Find(What:=txtFind.Text, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

Fiind from textbox and paste into sheet1
 

Thanks so much for your reply Jim. This is working but i still have a need
to past all of the contents of the row from which the cell was found. also i
would like to be able to search the entire workbook that contains 9 sheets,
and paste it in sheet 1. Thanks so much for your help.
-Mark

"Jim Thomlinson" wrote:

Here is some code that should work for you. I did not know where you wnated
the item pasted (paste regular or paste special for that matter) so I pasted
regular in the first blank cell in column a on the destination sheet. It is
important when doing a find to handle the case when it is not found. I just
did a message back to the user...

Private Sub cmdFind_Click()
Dim wksCopyFrom As Worksheet
Dim wksCopyTo As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngCopyTo As Range

Set wksCopyFrom = Sheets("Sheet1")
Set rngToSearch = wksCopyFrom.Cells
Set rngFound = rngToSearch.Find(txtFind.Text, , xlValues, xlWhole)
If rngFound Is Nothing Then
MsgBox "That item was not found."
Else
Set wksCopyTo = Sheets("Sheet2")
Set rngCopyTo = wksCopyTo.Cells(Rows.Count, "A").End(xlUp).Offset(1,
0)
rngFound.Copy rngCopyTo
End If

End Sub
--
HTH...

Jim Thomlinson


"Mark Cover" wrote:

Here is the problem. I am not able to get the info to paste into sheet1. I
can use the find function to find the cell but not able to paste. I would
also like to mention that I have multiple text boxes to search from. If
anyone can help i would greatly appreciate it. Thanks
-mark
Private Sub cmdFind_Click()
Dim R As Range
Dim Sheet As Worksheet
Sheets("sheet1").Select
Sheets("Sheet2").Select
Cells.Find(What:=txtFind.Text, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

Fiind from textbox and paste into sheet1
 

The best way to search all of the sheets depends on how many sheets you do
not want to search. Obviously you do not want to search the sheet you are
pasting to. You will also need to specify any other sheets that need to be
excluded in this code

Private Sub cmdFind_Click()
Dim wksCopyFrom As Worksheet
Dim wksCopyTo As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngCopyTo As Range

Set wksCopyTo = Sheets("Sheet1")
For Each wksCopyFrom In Worksheets
If wksCopyFrom.Name < wksCopyTo.Name And _
wksCopyFrom.Name < "Sheet10" Then 'sheets you don't want searched
Set rngToSearch = wksCopyFrom.Cells
Set rngFound = rngToSearch.Find(txtFind.Text, _
, xlValues, xlWhole)
If Not rngFound Is Nothing Then
Set rngCopyTo = wksCopyTo.Cells(Rows.Count, "A"). _
End(xlUp).Offset(1, 0)
rngFound.EntireRow.Copy rngCopyTo
End If
End If
Next wksCopyFrom
End Sub
--
HTH...

Jim Thomlinson


"Mark Cover" wrote:

Thanks so much for your reply Jim. This is working but i still have a need
to past all of the contents of the row from which the cell was found. also i
would like to be able to search the entire workbook that contains 9 sheets,
and paste it in sheet 1. Thanks so much for your help.
-Mark

"Jim Thomlinson" wrote:

Here is some code that should work for you. I did not know where you wnated
the item pasted (paste regular or paste special for that matter) so I pasted
regular in the first blank cell in column a on the destination sheet. It is
important when doing a find to handle the case when it is not found. I just
did a message back to the user...

Private Sub cmdFind_Click()
Dim wksCopyFrom As Worksheet
Dim wksCopyTo As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngCopyTo As Range

Set wksCopyFrom = Sheets("Sheet1")
Set rngToSearch = wksCopyFrom.Cells
Set rngFound = rngToSearch.Find(txtFind.Text, , xlValues, xlWhole)
If rngFound Is Nothing Then
MsgBox "That item was not found."
Else
Set wksCopyTo = Sheets("Sheet2")
Set rngCopyTo = wksCopyTo.Cells(Rows.Count, "A").End(xlUp).Offset(1,
0)
rngFound.Copy rngCopyTo
End If

End Sub
--
HTH...

Jim Thomlinson


"Mark Cover" wrote:

Here is the problem. I am not able to get the info to paste into sheet1. I
can use the find function to find the cell but not able to paste. I would
also like to mention that I have multiple text boxes to search from. If
anyone can help i would greatly appreciate it. Thanks
-mark
Private Sub cmdFind_Click()
Dim R As Range
Dim Sheet As Worksheet
Sheets("sheet1").Select
Sheets("Sheet2").Select
Cells.Find(What:=txtFind.Text, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate

Fiind from textbox and paste into sheet1
 

Jim,
IT WORKS GREAT!! Thanks for all of your help. I have been at this
for a while now. I am still a newbie. Thanks so much...
Mark

"Jim Thomlinson" wrote:

The best way to search all of the sheets depends on how many sheets you do
not want to search. Obviously you do not want to search the sheet you are
pasting to. You will also need to specify any other sheets that need to be
excluded in this code

Private Sub cmdFind_Click()
Dim wksCopyFrom As Worksheet
Dim wksCopyTo As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngCopyTo As Range

Set wksCopyTo = Sheets("Sheet1")
For Each wksCopyFrom In Worksheets
If wksCopyFrom.Name < wksCopyTo.Name And _
wksCopyFrom.Name < "Sheet10" Then 'sheets you don't want searched
Set rngToSearch = wksCopyFrom.Cells
Set rngFound = rngToSearch.Find(txtFind.Text, _
, xlValues, xlWhole)
If Not rngFound Is Nothing Then
Set rngCopyTo = wksCopyTo.Cells(Rows.Count, "A"). _
End(xlUp).Offset(1, 0)
rngFound.EntireRow.Copy rngCopyTo
End If
End If
Next wksCopyFrom
End Sub
--
HTH...

Jim Thomlinson


"Mark Cover" wrote:

Thanks so much for your reply Jim. This is working but i still have a need
to past all of the contents of the row from which the cell was found. also i
would like to be able to search the entire workbook that contains 9 sheets,
and paste it in sheet 1. Thanks so much for your help.
-Mark

"Jim Thomlinson" wrote:

Here is some code that should work for you. I did not know where you wnated
the item pasted (paste regular or paste special for that matter) so I pasted
regular in the first blank cell in column a on the destination sheet. It is
important when doing a find to handle the case when it is not found. I just
did a message back to the user...

Private Sub cmdFind_Click()
Dim wksCopyFrom As Worksheet
Dim wksCopyTo As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngCopyTo As Range

Set wksCopyFrom = Sheets("Sheet1")
Set rngToSearch = wksCopyFrom.Cells
Set rngFound = rngToSearch.Find(txtFind.Text, , xlValues, xlWhole)
If rngFound Is Nothing Then
MsgBox "That item was not found."
Else
Set wksCopyTo = Sheets("Sheet2")
Set rngCopyTo = wksCopyTo.Cells(Rows.Count, "A").End(xlUp).Offset(1,
0)
rngFound.Copy rngCopyTo
End If

End Sub
--
HTH...

Jim Thomlinson


"Mark Cover" wrote:

Here is the problem. I am not able to get the info to paste into sheet1. I
can use the find function to find the cell but not able to paste. I would
also like to mention that I have multiple text boxes to search from. If
anyone can help i would greatly appreciate it. Thanks
-mark
Private Sub cmdFind_Click()
Dim R As Range
Dim Sheet As Worksheet
Sheets("sheet1").Select
Sheets("Sheet2").Select
Cells.Find(What:=txtFind.Text, After:=ActiveCell, LookIn:=xlValues,
LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate