On Error GoTo not working

Metaripley

Board Regular
Joined
Dec 31, 2014
Messages
93
So ive got a autofilter with a VBA code to select the filtered data and copy it somewhere else.
But once the code has run a first time the 2nd time the autofilter is run it trows an error because there is no data (run time error 1004 no cell are found).
Until here it does what its supposed to do...
So I made an On Error Goto in the hope it would skip all next steps and end the macro.
But it does skip the select autofilter step, but it will continue all next steps and doesnt skip to the end of the macro as I thought it would.

VBA Code:
Sub Select_fliterd()
    
    'Select section
    On Error Resume Next
    ActiveSheet.Range("A2:M430").SpecialCells(xlCellTypeVisible).Select
    On Error GoTo endProc
    
    'Selection.Copy
    'go to location
    Sheets("Planning").Select
    Range("G1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    
    'past values only
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

endProc:
End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You need to put the Goto before the special cells not after it
VBA Code:
    On Error GoTo endProc
    ActiveSheet.Range("A2:M430").SpecialCells(xlCellTypeVisible).Select
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,215,873
Messages
6,127,459
Members
449,383
Latest member
DonnaRisso

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top