I want to copy a range having merged cells from Excel to Word. I am also hiding a few columns I don’t need in Word. My code works fine except that the merged cells become unmerged. How can I preserve the merged cells?
For Each boqWs In boqWb.Worksheets
If InStr(1, selectedTabNames, boqWs.Name) > 0 Then
lastRow = boqWs.Cells(boqWs.Rows.Count, "B").End(xlUp).row
' Define the range to copy (only visible columns)
Set boqRng = boqWs.Range("A1:N" & lastRow)
' hide columns in BOQ that dont need to be copied
boqWs.Range("C:E").EntireColumn.Hidden = True
boqWs.Range("G:G").EntireColumn.Hidden = True
boqWs.Range("I:I").EntireColumn.Hidden = True
boqWs.Range("K:M").EntireColumn.Hidden = True
' Copy visible columns
boqRng.Copy
' Paste into Word as a table
With rng
.PasteExcelTable False, False, True ' Paste the table with formatting
.Tables(1).AutoFitBehavior 2 'wdAutoFitWindow
End With
' Clear The Clipboard
Application.CutCopyMode = False
' Unhide columns if necessary
boqWs.Range("C:E").EntireColumn.Hidden = False
boqWs.Range("G:G").EntireColumn.Hidden = False
boqWs.Range("I:I").EntireColumn.Hidden = False
boqWs.Range("K:M").EntireColumn.Hidden = False
' Move to the next paragraph for subsequent additions
Set rng = rng.Tables(1).Range
rng.Collapse wdCollapseEnd
rng.InsertParagraphAfter
rng.Collapse Direction:=wdCollapseEnd
End If
Next boqWs
4