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.
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
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