Copy and paste file containing word in search cell


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")
     End With
 End With
End Sub


Selected Answer

You almost did it. Very near! This is all it takes.

.Offset(1).SpecialCells(xlCellTypeVisible).Copy Worksheets("Search").Range("F17")
.Offset(1, 5).SpecialCells(xlCellTypeVisible).Copy Worksheets("Search").Range("G17")


worked a dream, thank you for the help. makes sense now looking at it.
Daz1983 (rep: 6) Jul 18, '17 at 4:18 am
Thanks for the feedback. I'm glad it worked. Please mark my solution as having answered your question. That gives me reputation points and helps to guide others who might have a similar problem.
Variatus (rep: 823) Jul 18, '17 at 5:08 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login