A few years ago I posted my code for pasting values. It’s changed a bit since then. This morning, it looked like this:
gclsAppEvents.AddLog "^+v", "CopyPasteValues"
If TypeName(Selection) = "Range" And Application.CutCopyMode = xlCopy Then
Selection.PasteSpecial xlPasteValuesAndNumberFormats
ElseIf Application.CutCopyMode = xlCut Then
If Not ActiveSheet Is Nothing Then
ActiveSheet.Paste
End If
End If
End Sub
If I’m copying, then I paste both the values and the number formats (but not other formats, comments, data validation, etc). I found that this was my most common need. When I only want values, I use Alt+E+S like in the old days. If I’m cutting, I can’t PasteSpecial, so I just Paste.
Usually I select the first cell where I want to paste and press Ctrl+Shift+V and the selection expands to fit the data. Sometimes, however, that expanded selection contains merged cells which causes an error. I’ve just ignored the error in the past, but I figured it was time to fix it. Now my code looks like this:
gclsAppEvents.AddLog "^+v", "CopyPasteValues"
If TypeName(Selection) = "Range" And Application.CutCopyMode = xlCopy Then
On Error GoTo ErrHandler
Selection.PasteSpecial xlPasteValuesAndNumberFormats
ElseIf Application.CutCopyMode = xlCut Then
If Not ActiveSheet Is Nothing Then
ActiveSheet.Paste
End If
End If
ErrExit:
Exit Sub
ErrHandler:
Select Case Err.Number
Case 1004
If IsNull(Selection.MergeCells) Then
MsgBox "The range " & Selection.Address & " has merged cells. Can’t paste"
Else
MsgBox Err.Description
End If
Case Else
MsgBox Err.Description
End Select
Resume ErrExit
End Sub
Above the PasteSpecial line, I put an On Error statement to direct the program flow to an error handler. I didn’t apply that to the Cut portion of the code because cutting and pasting prompts me to unmerge cells and that’s the behavior I want.
In the error handling block, I check for the specific error 1004. I didn’t want to gloss over any old error, just this one in particular. If the error is 1004 (PasteSpecial method of Range class failed, or something like that), I then check to see if the new, expanded selection has any merged cells. While this is the only scenario that I’ve experienced that produces that error, it’s a pretty generic error and I’m sure there’s more. So I wanted to see the error description for any other errors.
To check for merged cells, I use IsNull(Selection.MergeCells). When the selection is more than one cell, the MergeCells property returns True if all the cells are merged, False, if none of the cells are merged, and Null if only some of the cells are merged. I don’t try to fix the situation, just display a somewhat meaningful error message. I don’t use merged cells all that often (it’s usually someone else’s spreadsheet, but not always). When I encounter this error, it’s usually because I copied more cells than I thought – hidden cells in the copy range. So it’s best to go back and start over.
At then end of the error block, it Resumes to ErrExit where it simply exits the sub. If there are no errors, the Exit Sub is executed before the ErrHandler label is reached.
