Hi,

I am facing a problem with copy and pasting a column that has a merged cell.
I have one closed workbook called sourceFile.xlsx
and I am using the following code to copy contents of the entire cell over to an open workbook:

Set wb = Workbooks.Open("sourceFile.xlsx")
wb.Worksheets("Sheet1").Columns("A").Copy
ThisWorkbook.Worksheets("destinationSheet").Columns("C").PasteSpecial Paste:=xlValues

wb.Worksheets("Sheet1").Columns("B").Copy 'causing issues with merged cells
ThisWorkbook.Worksheets("destinationSheet").Columns("D").PasteSpecial Paste:=xlValues

The first block of code works fine. It copies the contents of column A of the source file to column C of the destination file.
My problem is that for the second block of code, because column B in the source file has a merged cell somewhere, it won't copy and paste the column at all. It gives me an error message saying "Run-time error '1004': Cannot change part of a merged cell."

Now, if I were the owner of the sourceFile.xlsx, I would love to go "un-merge" the cells and be able to use the above code, but I am not allowed to change this sourceFile as it's a company-wide shared file.

Is there a quick way to fix this error?

Also, copying the entire column is time-consuming. Is there a clean and simple code that can copy and paste 'whatever is in the column' unlike the "Columns("A")" which copies the entire spreadsheet's column?

Thank you!