Partial Search on String to return Row

Hi,

I am attempting to search the values of Column A on the "data" sheet and return the values from Column A and G Onwards onto Sheet2.

I have the search function somewhat working as it is returning values that contain the inputted search parameter of Column A but seems to be duplicating when it find the search parameter in another column also.

Would really appreciate some help to get me the search results I am looking for.

Quote from royUK

I would be faster to do this with Find and FindNext, but faster still would be AutoFilter

Thanks Roy,

I am fairly new into using VBA, how exactly would I achieve this through the current macro I have created or creating a new one?

Quote from royUK

The typo sis in another module.

Try this

Code

Sub CopyData()
Dim rng As Range
Dim x As String


x = Worksheets("sheet2").Range("a1").Value


With Worksheets("data")
''///check whether a filter Is applied
If Not .AutoFilterMode Then .Range("A1").AutoFilter
.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:="=*" & x & "*" _
, Operator:=xlAnd
''///set a range = to visible cells (excluding the  header)
Set rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1)
rng.Copy Worksheets("sheet2").Cells(Worksheets("sheet2").Rows.Count, 3).End(xlUp).Offset(1)
End With
End Sub

Display More

Thanks Roy,

That seems to have sorted the search functionality and supplied me with the data required.

One little tweak that I am also looking to do is to remove rows B, C, D, E and F from the filtered search.

How would I go about implementing this?

Quote from royUK

I'm not sure what you mean Rows are numbers, columns are letters.

Sorry! I mean columns.

I wish to have the columns B, C, D, E and F removed from the search as the info isnt required

Yes.

So ultimately I am after the Name of the Document along with the cells that start to have ticks within them (column G) to only be shown in the Copied data (after the search has been performed)

Sorry for any confusion

Yep that is close to what I am looking to do.

All ticks do not seem to be showing though. E.G. The first 2 columns are after the document name but further down the columns are not displaying

The filtered down list after searching "front"

and

The results of the search where the tick for Column W on "data" sheet has not been copied over

Thanks,

That seems to have worked.

Last thing I am looking to do is remove the columns I - U from the final search outcome.

I have tried entering .Columns("I:U").EntireColumn.Delete much like the previous row to remove the beginning section from the search results but that doesnt seem to return the correct results.

How would I achieve this?

Indeed.

I commented that because as from the screenshots posted, the tick in column W did not show in the search (2nd screenshot).

Column W along with Column V didnt seem to have "shifted" down

Hope this makes sense?

Hi,

I have just done a random word search and it returns all of the documents, but they do not include the word inputted, for example "moose" returns all documents.

How would I go about implementing a fail safe error capture into this to return null or an error when the word inputted is not found.