excel - Pasting only Background/Number Formatting and Values with Merged Cells

Question

I am attempting to copy a specific region's contents and copy the Current Region back to a summary page.

I have unfortunately run into a bit of an issue with pasting the data back to the summary sheet.

Desired Outcome

Paste the following from my copied region to the summary page:

1. Maintain Merged Cells
2. Maintain Cell Highlight Colors
3. Cell Values

Copy Source

The source area has some formulas within it to gather some sheet data. See Picture Below:

Now whenever I go to paste the selected region, via means of my vba code

TitleBlockRange.CurrentRegion.Copy
nextEmptyCell.PasteSpecial (xlPasteFormats)       
nextEmptyCell.PasteSpecial (xlPasteValues)

I get the following error:

I have tried every single variation of PasteSpecial() that I can think of!

Is there a method that I am missing here? What method should I be using?

Thank you all in advance!

Answer 1

The curse of merged cells. But if you are stuck with them the code below will copy everything - just slower.

Sub CopyEverything()
    ' 153
    
    Dim SrcRng      As Range        ' Source range
    Dim Target      As Range        ' destination range
    Dim i           As Long         ' loop counter: index of SrcRng.Cells
    
    Set SrcRng = ActiveSheet.Cells(1, 1).CurrentRegion
    Set Target = Worksheets("Sheet2").Cells(10, 1) _
                .Resize(SrcRng.Rows.Count, SrcRng.Columns.Count)
    
    Application.ScreenUpdating = False
    With SrcRng
        .Copy
        Target.PasteSpecial xlPasteFormats
    
        For i = 1 To .Cells.Count
            With .Cells(i)
                If .Address = .MergeArea.Cells(1).Address Then
                    Target.Cells(i).Value = .Value
                End If
            End With
        Next i
    End With
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

Bear the following in mind.

• To copy row heights you must copy entire rows.
• To copy column widths you must copy entire columns.

The above task would be easier if you could copy entire rows or entire columns, or the entire sheet. However, if you deploy the above code you already have the infrastructure to copy individual settings and it shouldn't take more than a few extra lines to add a transfer of those dimensions which are important to you.