How to transfer Rows from the results of "Find' function in Excel 2010 to another sheet

Dear Friends

I have a user with a excel workbook. What the user wants is that after using the "Find & Select" function in excel to search of certain texts/data/number, the results will be shown as attached (see picture) if found in the sheet. Can the entire rows of the results found be copied onto another sheet after that? I am not great at either VB or macros. So hopefully someone can help me out with this.
Find-and-Replace.JPG
LVL 1
jimjosephAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Can the entire rows of the results found be copied onto another sheet after that?

Not directly.

Visual Basic for Applications [VBA] code could be written to emulate the "Find and Replace" function & then copy the rows where a match is found, but the results within the dialog box are not able to be manipulated other than clicking on the cell references, or selecting multiple rows in the results.

You could, however, use the in-built "Find and Replace" function, highlight all the results (so that the individual cells are then selected on the associated worksheet), & then run some VBA code that copies the selection to a predefined worksheet.

BFN,

fp.
0
[ fanpages ]IT Services ConsultantCommented:
The following code is taken from the attached workbook's (Public) code module, "basQ_28248974":

Option Explicit
Public Sub Q_28248974()

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28248974.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               Q_28248974
' Question Title:   How to transfer Rows from the results of "Find' function in Excel 2010 to another sheet
' Question Asker:   jimjoseph                                 [ http://www.experts-exchange.com/M_4383730.html ]
' Question Dated:   2013-09-25 at 10:18:00
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

  Dim blnCopied_Row()                                   As Boolean
  Dim lngRow                                            As Long
  Dim lngErr_Number                                     As Long
  Dim objCell                                           As Range
  Dim strErr_Description                                As String
  
  On Error GoTo Err_Q_28248974
  
  ReDim blnCopied_Row(0&) As Boolean
  
  If ActiveSheet.Name = "Original" Then
     Application.ScreenUpdating = False
     
     lngRow = Worksheets("Copied").Cells(Cells.Rows.Count, 1).End(xlUp).Row
     
     For Each objCell In Selection
     
         If objCell.Row > UBound(blnCopied_Row) Then
            ReDim Preserve blnCopied_Row(objCell.Row) As Boolean
         End If ' If objCell.Row > UBound(lngRow_Source) Then
         
         If Not (blnCopied_Row(objCell.Row)) Then
            objCell.EntireRow.Copy Destination:=Worksheets("Copied").Rows(lngRow)
            blnCopied_Row(objCell.Row) = True
            lngRow = lngRow + 1&
         End If ' If Not (blnCopied_Row(objCell.Row)) Then
         
     Next objCell
  
     Beep
  End If ' If ActiveSheet.Name = "Original" Then
  
Exit_Q_28248974:

  On Error Resume Next
  
  Erase blnCopied_Row()
  ReDim blnCopied_Row(0&) As Boolean
  
  Set objCell = Nothing
  
  Application.ScreenUpdating = True

  Exit Sub
  
Err_Q_28248974:

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  
  On Error Resume Next
  
  MsgBox "Error #" & CStr(lngErr_Number) & _
         vbCrLf & vbLf & _
         strErr_Description, _
         vbExclamation Or vbOKOnly, _
         ThisWorkbook.Name
         
  Resume Exit_Q_28248974
  
End Sub

Open in new window



The workbook has two worksheets: [Original] & [Copied].

The [Original] worksheet contains random numbers (in the range 1 to 10) in the range [A1:J40] just as test data.

The [Copied] worksheet contains no data.

If you use the "Find and Replace" ("Find All") function to search for a number (from 1 to 10 inclusive), then select one or more rows of the results shown, the associated [Original] worksheet will select the matching cells (as is normal MS-Excel functionality).

However, if you now click back onto the main MS-Excel window, & use the [ALT]+[F8] key combination to display the "Macro" dialog window, you can select "Q_28248974" as the "Macro name".  Click the [Run] button to execute the (above) code.

Any row with a selected cell will then be copied to the [Copied] worksheet.

Note: You do not need to use the "Find and Replace" function first, you could simply select one or more cells on the [Original] worksheet & run the "Q_28248974" macro (VBA) code thereafter.

BFN,

fp.
Q-28248974.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
Have you looked at the Advanced Filter function?

You can use this option to analyse a table of data and pull out rows which meet certain criteria. The rows can be shown in place or copied to another sheet.

Thanks
Rob H
0
[ fanpages ]IT Services ConsultantCommented:
Sigh.

14 January 2014: A grading of 'B' (good) without any further communication from the question asker after my proposed solution posted almost four months ago.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.