VBA Ignoring On Error GoTo Command.. XL 2010

jxd078

New Member
Joined
Sep 18, 2015
Messages
3
Hi,

Relatively new to VBA, but having read up on using On Error GoTo commands I cannot understand how the code is not responding when it encounters an error.
The most annoying part is to get to that part of the code it has already correctly been navigated using an On Error statement.
I'm sure it's really just to do with how I've structured/ordered my code.

Code:
Function PasteUS()


On Error GoTo NextTry
ActiveSheet.PasteSpecial Format:="SYLK", Link:=False, DisplayAsIcon:= _
        False


LetsContinue:
Exit Function


NextTry:
On Error GoTo ErrHandler
[COLOR=#ff0000]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, _[/COLOR]
[COLOR=#ff0000]        Transpose:=False[/COLOR]
    Application.CutCopyMode = False
Resume LetsContinue


ErrHandler:
MsgBox errstring & " " & Err.Description
Resume LetsContinue


End Function
The error context:

User is copying data from an external source.

In the sub which calls this function, there is a function that checks if the clipboard is empty, so to get to this code VBA has deemed the clipboard is NOT empty.

However the error message only occurs when it seems like nothing has been copied. For example copy an area of data in another instance of excel (which is subsequently surrounded by the black and white moving border).
If you press Esc on the keyboard, the border disappears, and returns to the range just being a highlighted selection.

But stepping through the macro, the clipboard passes the not empty test, then errors when it gets to the red text in the code above.
The error is 'PasteSpecial method of Range class failed'.

Any ideas why the code is not being redirected to the Error Handler when this error occurs?
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,368
Office Version
2019, 2016, 2013
Platform
Windows
If its truly empty then I've had issues with paste special, nothing there to paste hence error
 

jxd078

New Member
Joined
Sep 18, 2015
Messages
3
I understand why the error is occurring, the real question is why that doesn't prompt it to move to the error handler, as directed
 

Watch MrExcel Video

Forum statistics

Threads
1,102,858
Messages
5,489,306
Members
407,686
Latest member
Chuck1960

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top