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.
Who is Participating?
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
The following code is taken from the attached workbook's (Public) code module, "basQ_28248974":

Option Explicit
Public Sub Q_28248974()

' --------------------------------------------------------------------------------------------------------------
' [ ]
' 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                                 [ ]
' Question Dated:   2013-09-25 at 10:18:00
' Expert Comment:   fanpages                                   [ ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ ]
' --------------------------------------------------------------------------------------------------------------

  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
  End If ' If ActiveSheet.Name = "Original" Then

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

  Exit Sub

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  On Error Resume Next
  MsgBox "Error #" & CStr(lngErr_Number) & _
         vbCrLf & vbLf & _
         strErr_Description, _
         vbExclamation Or vbOKOnly, _
  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.


[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:

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.


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.

Rob H
[ fanpages ]IT Services ConsultantCommented:

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.