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
Bookmarks