VBA Code to copy and paste active column with merged cells inside

Question

I'm trying to copy the active column and paste it next to it but the code selects the entire worksheet because it has merged cells in.

Sub CopyPaste()

Columns(ActiveCell.Column).Selection
Selection.Copy
ActiveCell.Offset(0,1).PasteSpecial Paste:=xlPasteAll

End Sub

Could you please help me adding the missing code to ignore merged cells?

What does `Columns(ActiveCell.Column).Selection` do? – Apr 11 '17 at 15:05 — , Apr 11 '17 at 15:05

score 5 · Accepted Answer · answered Apr 11 '17 at 15:07

5

This is yet another reason to avoid using Select in VBA for Excel. Your selection will expand with the merged cells. You can try this:

ActiveCell.EntireColumn.Copy ActiveCell.Offset(0, 1).EntireColumn

And again, you should find some way to avoid counting on the ActiveCell in your code, and use some fully qualified range.

answered Apr 11 '17 at 15:07

A.S.H

Thanks for the help. I'm not used to code without Select, need to change to new coding without select – Maxwell Apr 11 '17 at 15:11
1

@Maxwell - See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – Apr 11 '17 at 15:13
1

@Maxwell The SO Excel-VBA documentation might also help you, e.g. the [best practices](http://stackoverflow.com/documentation/excel-vba/1107/vba-best-practices#t=201704111544413447144). – SteveES Apr 11 '17 at 15:45
Thanks guys I forgot to ask in mi question. I want to after copying the column, to copy and paste the active column in values – Maxwell Apr 11 '17 at 16:12
You mean you want to copy only values without the formats? In that case you can try this: `ActiveCell.Offset(0, 1).EntireColumn.value = ActiveCell.EntireColumn.value` – A.S.H Apr 11 '17 at 16:33
@A.S.H With this code, the column stays in values but the numbers increase to two decimals. In the other hand when I copy and select paste in values from the Home bar, it keeps the numbers without decimals. Do you know what do I have to add to this line of code to keep the exact format without two decimals? Also the in some cells of the column, it has dates. – Maxwell Apr 19 '17 at 13:04

1 Answers1