Hi,
That does indeed bring up an error message but seems to do it for any word you enter, even if it has values to return. E.G. I entered front and it returned the error but should instead populate the table and not return an error.
Thanks
Hi,
That does indeed bring up an error message but seems to do it for any word you enter, even if it has values to return. E.G. I entered front and it returned the error but should instead populate the table and not return an error.
Thanks
This should work
Option Explicit
Sub CopyData()
Dim tempSht As Worksheet
Dim rng As Range, chk As Range
Dim x As String
x = Worksheets("sheet2").Range("a1").Value
Set chk = Worksheets("data").Range("A1").CurrentRegion.Columns(1).Cells.Find(x)
If chk Is Nothing Then
MsgBox x & " is not in the database", vbCritical, "Try again"
Exit Sub
End If
Worksheets.add(After:=Worksheets(Worksheets.Count)).Name = "Temp"
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)
With ActiveSheet
rng.Copy.Range ("A1")
.Columns("B:F").EntireColumn.Delete
.Range("A1").Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 19).Copy Worksheets("sheet2").Cells(Worksheets("sheet2").Rows.Count, 3).End(xlUp).Offset(1)
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
End With
End Sub
Display More
Hi,
Yep that seems to work a lot better.
Noticed 1 thing though, I am having to go into the data tab to reset the filter each tome, otherwise it only searches on the filtered down data.
E.G I search Front, data sheet filters down to only show Front. I reset and then search Blood, doesn't find it as only Front is filtered down still.
How do I reset the data sheet filter to show all data again on reset?
I've added code to clear the AutoFilter and made a couple of slight changes to improve the code
Don’t have an account yet? Register yourself now and be a part of our community!