escaping from coding

[MAMIBUSSOL]

i am running a piece of code which filters some data based on an account
I am then copying this data to another worksheet, this code does this if it has matching criteria, if the filter contains no results, it still works but it copies every piece of data.

    With ActiveWorkbook.Worksheets("CRED")
        .Range("$A$1:$IE$388").AutoFilter Field:=3, Criteria1:="=ADVERT"
        .Range("$A$3:$G$388").Copy Sheets("TRIAL").Range("A" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
    End With

is it possible to adjust the above code to drop out of the with statement if the filter produces NO results and not copy any data, so in essence bypass this line of code

        .Range("$A$3:$G$388").Copy Sheets("TRIAL").Range("A" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)

 

[Sal Paradise]

Try this line before the copy:

If .AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible).Count > 0 Then
    .Copy Sheets("TRIAL").Range("A" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
End If

 

[AlphaFrog]

With ActiveWorkbook.Worksheets("CRED")
    If Not .Range("C1:C388").Find("ADVERT", , xlValues, xlWhole, , , False) Is Nothing Then
        .Range("A1:IE388").AutoFilter Field:=3, Criteria1:="=ADVERT"
        .Range("A3:G$388").Copy Sheets("TRIAL").Range("A" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
    End If
End With

 

[MAMIBUSSOL]

Tried your adjustment, got a debug error

copy method of worksheet class failed

i am assuming you was think of the following adjustment to the code

    With ActiveWorkbook.Worksheets("CRED")
        .Range("$A$1:$IE$388").AutoFilter Field:=3, Criteria1:="=ADVERT"
        If .AutoFilter.Range.Rows.SpecialCells(xlCellTypeVisible).Count > 0 Then
            .Copy Sheets("TRIAL").Range("A" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
        End If
    End With

if I adjust your code to this I get rid of the error, however the same happens

.Range("$A$3:$G$388").Copy Sheets("TRIAL").Range("A" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)

 

[MAMIBUSSOL]

AlphaFrog same problem but in reverse, I get no data transferred, when I step through the code it exits the if statement

 

[MAMIBUSSOL]

sorry alphafrog, mistype on my part, will do some testing on my code and come back if still a problem

 

[MAMIBUSSOL]

I spoke too soon AlphaFrog

I've adjusted my coding to accommodate your suggestions, however, its being picky

    ActiveWorkbook.Worksheets("CRED").Select
    With ActiveWorkbook.Worksheets("CRED")
        If Not .Range("C1:C388").Find("ADVERT", , xlValues, xlWhole, , , False) Is Nothing Then
            .Range("A1:IE388").AutoFilter Field:=3, Criteria1:="=ADVERT"
            .Range("A3:G$388").Copy Sheets("TRIAL").Range("A" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
        End If
    End With
    With ActiveWorkbook.Worksheets("CRED")
        If Not .Range("C1:C388").Find("B DEPREC", , xlValues, xlWhole, , , False) Is Nothing Then
            .Range("A1:IE388").AutoFilter Field:=3, Criteria1:="=B DEPREC"
            .Range("A3:G$388").Copy Sheets("TRIAL").Range("J" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
        End If
    End With
    With ActiveWorkbook.Worksheets("CRED")
        If Not .Range("C1:C388").Find("BAR EQUIP", , xlValues, xlWhole, , , False) Is Nothing Then
            .Range("A1:IE388").AutoFilter Field:=3, Criteria1:="=BAR EQUIP"
            .Range("A3:G$388").Copy Sheets("TRIAL").Range("S" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
        End If
    End With
    With ActiveWorkbook.Worksheets("CRED")
        If Not .Range("C1:C388").Find("BAR SUNDRIES", , xlValues, xlWhole, , , False) Is Nothing Then
            .Range("A1:IE388").AutoFilter Field:=3, Criteria1:="=BAR SUNDRIES"
            .Range("A3:G$388").Copy Sheets("TRIAL").Range("S" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
        End If
    End With

there are no records for ADVERT & B DEPREC however BAR EQUIP & BAR SUNDRIES have records.

the coding seems to work for BAR EQUIP, but the BAR SUNDRIES doesn't

 

[AlphaFrog]

    Application.ScreenUpdating = False
    ActiveWorkbook.Worksheets("CRED").Select
    Range("A1:IE388").AutoFilter 'Reset autofilter
    If Not Range("C1:C388").Find("ADVERT", , xlValues, xlWhole, , , False) Is Nothing Then
        Range("A1:IE388").AutoFilter Field:=3, Criteria1:="=ADVERT"
        Range("A3:G388").Copy Sheets("TRIAL").Range("A" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
    End If
    Range("A1:IE388").AutoFilter 'Reset autofilter
    If Not Range("C1:C388").Find("B DEPREC", , xlValues, xlWhole, , , False) Is Nothing Then
        Range("A1:IE388").AutoFilter Field:=3, Criteria1:="=B DEPREC"
        Range("A3:G388").Copy Sheets("TRIAL").Range("J" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
    End If
    Range("A1:IE388").AutoFilter 'Reset autofilter
    If Not Range("C1:C388").Find("BAR EQUIP", , xlValues, xlWhole, , , False) Is Nothing Then
        Range("A1:IE388").AutoFilter Field:=3, Criteria1:="=BAR EQUIP"
        Range("A3:G388").Copy Sheets("TRIAL").Range("S" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
    End If
    Range("A1:IE388").AutoFilter 'Reset autofilter
    If Not Range("C1:C388").Find("BAR SUNDRIES", , xlValues, xlWhole, , , False) Is Nothing Then
        Range("A1:IE388").AutoFilter Field:=3, Criteria1:="=BAR SUNDRIES"
        Range("A3:G388").Copy Sheets("TRIAL").Range("S" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)
    End If
    Worksheets("CRED").AutoFilterMode = False 'Turnoff autofilter
    Application.ScreenUpdating = True