Excel 2007 : Filtered Copy Macro to continue if it comes across an error

**Nikeyg** · 04-01-2011, 11:27 AM

I have this macro which is to filter a set of data by #N/A entries, then copy and paste them into the first available blank cell in a different worksheet.

Sub DetermineGoodTable()

'
' DetermineGoodTable Macro
'

'
    ActiveSheet.Range("$A$1:$D$66").AutoFilter Field:=2, Criteria1:="#N/A"
    Columns("B:B").Select
    Selection.ClearContents
    Dim Summary_Range As Range
    Set Summary_Range = Worksheets("Sheet5").AutoFilter.Range

    With Summary_Range
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Copy
End With

    Sheets("Sheet2").Select
    Range("D1").Select
        Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
End Sub

The problem here is, that there won't always be data to be filtered to #N/A as as such there will be nothing to copy.
As such an error occurs saying that there is no data.
Is it possible to code the macro so that if it can't find data then it should just continue with what it was doing?

I plan for the macro to continue on after this, back in Sheet 2. So, essentially, it probably needs to understand that if there's no data then it should just dump the macro back to sheet 2 so it can continue with what it was doing?

**TMS** · 04-01-2011, 11:29 AM

What do you want it to copy and paste if there are no rows selected?

Regards

**Nikeyg** · 04-01-2011, 11:32 AM

I don't.
I just don't want the macro to stop if it can't find data.
Essentially, if it can't find data to copy, I want it to go to Sheet 2 so the macro can continue what it needs to do to compile the rest of the data, but if it can find data to copy it needs to copy and paste that data where required. In this case, Sheet2.

**TMS** · 04-01-2011, 11:51 AM

To stop error trapping, use:

On Error Resume Next

and, to start it again:

On Error Goto 0

However, I suspect the paste special | values will fail if the copy has failed or not been executed.

Regards

**Nikeyg** · 04-01-2011, 12:02 PM

This is what I did with your code and it works.
If it finds data it pastes it properly, if not, it still goes to Sheet2, but doesn't paste anything or return an error.

Sub DetermineGoodTable()

'
' DetermineGoodTable Macro
'

'
    On Error Resume Next
    
    ActiveSheet.Range("$A$1:$D$66").AutoFilter Field:=2, Criteria1:="#N/A"
    Columns("B:B").Select
    Selection.ClearContents
    Dim Summary_Range As Range
    Set Summary_Range = Worksheets("Sheet5").AutoFilter.Range

    With Summary_Range
        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Copy
End With

    Sheets("Sheet2").Select
    Range("D1").Select
        Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
    On Error GoTo 0
    
End Sub

Thanks Shucks