Paste Values Error 2015

Partjob

Board Regular
Joined
Apr 17, 2008
Messages
139
Hi
My Problem is Error 2015, I always get this error whether there is data to paste or not in the following code.
Sometime ago I got a bit of help with this and thought I had it licked however I seem to have some further problems.
Code:
Sub PastSpec()
Application.EnableEvents = False
On Error GoTo ErrHan:
'Work out the size of the copied range
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Rcount = Selection.Rows.Count
Ccount = Selection.Columns.Count
'Check for locked cells
For Each c In Selection.Resize(Rcount, Ccount)
    If c.Locked = True Then
    MsgBox ("You have locked cells in your destination"), vbCritical, "Paste Values"
    Application.EnableEvents = True
    Exit Sub
    End If
    Next c
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Application.EnableEvents = True
ErrHan:
If xlErrValue = 2015 Then Exit Sub
MsgBox ("You have nothing to paste"), vbOKOnly, "Paste Values"
ApplicationbleEvents = True
Exit Sub
End Sub

This is quite straight forward really. The user copies something from another workbook or tab and only wants the values. I wanted to create a macro that enable them to do this easily.
Before the data went in though I needed the code to check for locked cells at the destination. It does this by pasting in the column widths(They would be the same) first and then checking the size of the range before actually putting in the values. I then needed a way of informing the user that they had not copied anything hence the Error handler. The msgbox should say "you have not copied anything" but I have changed it to display the error at the moment. The Error handler kicks in whether there is data to paste or not. I don't really understand why?

Just to clarify I need the code to check there something to paste and check the destination for locked cells (this works) before pasting in the data.

Thanks in advance
Partjob
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

xlHammer

Board Regular
Joined
Apr 7, 2008
Messages
215
You need an 'Exit Sub' command before your ErrHan code otherwise this code will execute automatically when it is reached at the end of your main code.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,672
Messages
5,654,670
Members
418,147
Latest member
dorkas

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
Top