Results 1 to 14 of 14

Macro to Search and Copy Multiple Keywords into Another Sheet

Threaded View

  1. #1
    Registered User
    Join Date
    05-14-2010
    Location
    The Midwest, USA
    MS-Off Ver
    Excel 2007
    Posts
    18

    Question Macro to Search and Copy Multiple Keywords into Another Sheet

    I’d like to alter the current macro I have pasted below but I’m having trouble identifying where I should add/change lines in the code. Maybe you can help? Here are the details:

    Ideally, I’d like this macro I pasted below to search for keywords listed in column AK (there are multiple keywords found in each cell of this column, each separated by commas within the cell) of worksheet titled 'Octopus".

    Once the macro has identified which rows contain keywords in column AK (the macro currently has an input box for the user to type the keyword into) I’d like the macro to copy these rows (information from columns A-AK would need to be copied) and paste them into sheet named Sheet1.

    • The macro below doesn’t search for multiple keywords at a time which isn’t ideal.
    • It doesn’t copy over the comment box information from these rows (comments are located in columns D and E of each row) into Sheet1.
    • It doesn't allow users to input more than one keyword into the input box at a time, but should copy rows matching both keywords that were searched.
    • It does correctly copy the header row from sheet Octopus (row 4) and paste it into Sheet1 and I'd like that to stay.
    • Copy entire rows with their information including their comment boxes over into Sheet1
    • Allow searching for multiple keywords matches to be searched at once,
      copy
      over rows that match both keywords at a time into Sheet1

    Sub SearchKeywords()
        Dim vData
        Dim vFoundData()
        Dim j As Long
        Dim i As Integer
        Dim sSearch As String
        
         'Note this is the parent sheet and should be All Levels Combined
        vData = Sheets(1).UsedRange.Value
        ReDim vFoundData(1 To UBound(vData))
        sSearch = InputBox("Enter search string")
        For j = 1 To UBound(vData, 1)
            For i = 1 To UBound(vData, 2)
                If InStr(1, vData(j, i), sSearch, 1) Then vFoundData(j) = 1
            Next i
        Next j
        For j = 1 To UBound(vData, 1)
            If Not vFoundData(j) = 1 Then
                For i = 1 To UBound(vData, 2)
                    vData(j, i) = ""
                Next i
            End If
        Next j
        
         'This is where the copying takes place
        With Sheets(14)
            .Select
            .Range(Cells(1, 1), Cells(UBound(vData, 1), UBound(vData, 2))).Select
            Selection = vData
            Selection.Sort Range("a1")
            .Range("a1").Select
        End With
        Selection.Copy
        Sheets("Sheet1").Select
        ActiveWindow.SmallScroll Down:=-24
        Rows("1:1").Select
        Application.CutCopyMode = False
        Selection.Insert Shift:=xlDown
        Range("A1").Select
        Sheets("All Levels - Combined").Select
        ActiveWindow.ScrollColumn = 20
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 13
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 1
        Rows("4:4").Select
        Selection.Copy
        ActiveWindow.ScrollWorkbookTabs Position:=xlLast
        Sheets("Sheet1").Select
        Range("A1").Select
        ActiveSheet.Paste
        Range("A1").Select
        Cells.Select
        Cells.EntireColumn.AutoFit
        Range("A1").Select
    End Sub
    Last edited by Bangarang; 08-05-2010 at 10:54 AM. Reason: Solved issue by Foxguy

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1