none
Multiple Selection and Pasting RRS feed

  • Question

  • I am trying to use a userforum to search specific keywords and to select all the cells containing that information and to copy and paste the entire row into another sheet. So far I only know how to select one cell/row at a time. This is what I have so far. 

                   

    Private Sub CommandButton1_Click()


    Cells.Find(What:=searchbox1.Text, After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    True, SearchFormat:=False).Activate

           ActiveCell.EntireRow.Select

           Selection.Copy

    Thursday, June 30, 2016 7:18 PM

All replies

  • Re:  finding stuff
    Here is some generic code that may help.
    It loops until nothing is found.

    '---
    Sub AnotherTest()
    Dim C As Range
    Dim x As Long: x = 2
    Dim firstAddress As String

    With Worksheets(2).UsedRange
      Set C = .Find("Sludge", LookIn:=xlFormulas)
      If Not C Is Nothing Then
          firstAddress = C.Address
          Do
            C.EntireRow.Copy Destination:=Worksheets(3).Cells(x, 1)
            x = x + 1
            Set C = .FindNext(C)
            If C Is Nothing Then Exit Do
          Loop Until C.Address = firstAddress
      End If
    End With
    End Sub
    '---

    My commercial 'Find & List' Excel add-in (xl97 to xl2010) can be downloaded from...
    https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0
    and tried out. - no ads, no cookies, no trackers, no registration
    It has about 1400 lines of code.
    '---
    Jim Cone
    Portland, Oregon USA




    • Edited by James Cone Wednesday, September 21, 2016 10:21 PM
    Friday, July 1, 2016 2:04 AM