Currently I'm using this code to attempt at copying and pasting files from a worksheet named "File manager" to "Search", however I only want to copy and paste file that contain a word that is entered into Search worksheet "B14", I am only using a single word, so it must copy and paste any file name that contains this word in the title.
At the moment this VBA code copies everything and not just the rows containing the searched file name.
Private Sub CommandButton1_Click()
Dim strF As String
On Error Resume Next
With Worksheets("Search")
Intersect(.UsedRange, .Range("F17:F" & .Rows.Count)).ClearContents
Intersect(.UsedRange, .Range("G17:G" & .Rows.Count)).ClearContents
strF = .Range("B14").Value
End With
On Error GoTo 0
With Worksheets("File Manager")
With .Range(.Range("C13"), .Cells(.Rows.Count, "C").End(xlUp))
.AutoFilter Field:=1, Criteria1:="=*" & strF & "*", Operator:=xlAnd
.Offset(1).Copy Worksheets("Search").Range("f17")
.Offset(1, 5).Copy Worksheets("Search").Range("g17")
.AutoFilter
End With
End With
End Sub