I'm going to take a stab at this as I think I've tested enough with your code to understand what's going on.
Let's start with how Excel is handling the various named ranges when performing the actions you have designated. You can see that after setting copyRange to the MERGE_THEN_NAME constant, and using .Copy (for testing purposes) on the newly defined copyRange, the A2 cell is has the moving dashed lines around it.
Option Explicit
Public Sub PerformTests()
Const NAME_THEN_MERGE As String = "Name_Then_Merge"
Const MERGE_THEN_NAME As String = "Merge_Then_Name"
Const PASTE_NAME_THEN_MERGE As String = "Paste_Name_Then_Merge"
Const PASTE_MERGE_THEN_NAME As String = "Paste_Merge_Then_Name"
TestNames MERGE_THEN_NAME, PASTE_MERGE_THEN_NAME
'/ Result: Error 1004, cannot do that to a merged cell
End Sub
Public Sub TestNames(ByVal copyName As String, ByVal pasteName As String)
Sheets("wsPasteTest").Activate
Dim copyRange As Range, pasteRange As Range
Set copyRange = Sheets("wsPasteTest").Range(copyName)
copyRange.Copy 'This is the last step executed before error
Set pasteRange = Sheets("wsPasteTest").Range(pasteName)
CopyPasteCell copyRange, pasteRange
End Sub
This is expected since the defined range is read as =wsPasteTest!$A$2 in the Name Manager.
This definition of the range means that when Excel revisits the named range to extract a value, it does so by interpreting the address literally, i.e. it only considers the cell value of A2 since the merged cells' value is defined in the "top left" cell address of itself. This logic can be confirmed by the correct method of returning the value of a merged cell through VBA procedures (which is addressed in the above hyperlink) by needing to use something like Range("A2:E2").Cells(1).Value rather than simply Range("A2:E2").Value. The latter option returns an array because it is considering each individual cell's value in the range.
The next part here is why it can be dangerous to use .Select or .Activate through your whole code, but also shows how to get the behavior you would like. We can see when we .Select the copyCell that it selects the whole merged cell compared to just A2 in the .Copy method (which mimics how a user interacts with the range on the spreadsheet itself). So now, if we select copyCell and then use Selection.Copy, the whole merged cell is copied rather than just A2 since Excel has been instructed to grab the whole array area. Excel has also just shown how significant selecting an item and then using the selection can drastically change its interpretation of a range rather than explicitly working with the range.
The code in PerformTests() and TestNames() is identical to yours, the following sub was modified:
Public Sub CopyPasteCell(ByRef copyCell As Range, ByRef pasteCell As Range, Optional ByVal pasteRowHeights As Boolean = False)
copyCell.Select
Selection.Copy 'this step is shown in the first screenshot below
pasteCell.Select
Selection.PasteSpecial xlPasteAll 'this step is shown in the second screentshot below
If pasteRowHeights Then
Dim sourceRowHeight As Long
sourceRowHeight = copyCell.RowHeight
pasteCell.RowHeight = sourceRowHeight
End If
End Sub
Here's the result of the copyCell being selected, then copied.
Here's the result of the pasteCell being selected, then pasted into. This shows the result you desired while using Merge_Then_Name range.
Ultimately, you can see that when you refer to a named range explicitly that has been defined after merging, it interprets the named range's address literally (giving a single cell value with single cell formatting) which clearly cannot be pasted into the area size desired using the pasting method suggested. If you change your pasting method to xlPasteValues you can avoid using .Select. The reason for this is because it allows the definition of Paste_Merge_Then_Name to remain as =wsPasteTest!$A$8 while also allowing for the value to be transferred to a single cell area. This method keeps the destination cell's formatting the same while just replacing the value. It is not exactly what your intended paste was, but quite close.
Public Sub CopyPasteCell(ByRef copyCell As Range, ByRef pasteCell As Range, Optional ByVal pasteRowHeights As Boolean = False)
copyCell.Copy
pasteCell.PasteSpecial xlPasteValues
If pasteRowHeights Then
Dim sourceRowHeight As Long
sourceRowHeight = copyCell.RowHeight
pasteCell.RowHeight = sourceRowHeight
End If
End Sub
The Name_Then_Merge range works with both single cell and the named range pastes for one main reason; how many placeholders it contains for values. When you do the .Copy method of Name_Then_Merge, it selects the whole merged cell because it's instructed to by its definition =wsPasteTest!$A$5:$E$5. This is good because pasting this into an equal sized named range would work because they match, and pasting into a single cell would work because there's a single non-blank value.
Summary: The named ranges are defined by their initial cell values and hold that number of "places" even after cells are merged. So if you name a range, it references the values of the each cell in a range (5 values in this case), and merging the cell does not change the definition of the named range (but changes all values of the array after the first cell to "" as a placeholder). If you merge cells and then name the range, the value is held in the top left cell of the merged area, and is therefore used as the definition of the merged cell for the named range. In this case, Excel gets confused if you try to just .Paste or xlPasteAll this into a merged cell because it'll copy the value of the source as a single cell area and attempt to paste it into the destination which is a five cell area.
Solve this by making sure to copy the whole area of the named range by selecting it or by using a different paste method but be wary of what these paste methods produce. E.g. xlPasteAllUsingSourceTheme will unmerge the destination cells but still satisfies the named range definition.
