Paste Values VBA

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
2,332
Office Version
  1. 365
Platform
  1. Windows
I have a table where the user can past in a list. In my directions I ask that when they paste in a list (of Part Numbers) that they copy the list from their other source and then use a button I have on the tab to paste in the data.

This code works. But if the did not copy data from their other source and hit the button they get an error message - which makes sense. There's no data to paste

How can I modify this code so the error code doesn't pop-up?

Code:
Sub PasteItemID()
'For User to Paste in a list of Part Numbers
    Range("Table2[ItemID]").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("B26").Select
    
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
What is the error message/number that pops up?

You will want to get that number, and add error handling to your code to ignore that error.

If the error number is "1004", then the code would look something like this:
VBA Code:
Sub PasteItemID()
'For User to Paste in a list of Part Numbers

    On Error GoTo err_chk
    Range("Table2[ItemID]").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("B26").Select
    On Error GoTo 0
    
    Exit Sub
    
err_chk:
    If Err.Number = 1004 Then
        Err.Clear
        Resume Next
    Else
        MsgBox Err.Number & ": " & Err.Message
    End If
    
End Sub
 
Upvote 0
Solution
Run-time error '1004':

PasteSpecial method of range class failed
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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