excel merged cells copy paste issue

0
votes

copy the merged cell and paste into the unmerged cell causing merging of pasted area also. example: A1,B1 are merged together. now I copied this range and pasted in D1. since the copied range is merge format, the pasted area becoming like D1:E1. D1, E1 are merged after pasting data. I want to paste data only in D1. how to solve this issue using VBA

2
excelvba
Could you show what you got so far? It seems that you copiede the cells and not the value. – snenson
the manual copy paste is happening in one of my Excel sheet. when copy paste merged cell , I don't want merged the pasted area. the pasting has to limit to single cell. – learnTech
You cant change the behavior of the excel copy-paste, you can copy the values like @Lawrence shows it or you unmerge the cells after the copy-paste with a macro. – snenson
my excel template users will do copy paste activity. the copy paste is randomly happens, not the fixed area. so I thought if any work around available to fix this. – learnTech
I dont think there is a viable option to to that. You could check the Change events of excel, but they look for every chage that happens in the workbook but i dont think its a good option. – snenson

2 Answers

0
votes

Maybe you can get text of area A1:B1, then set Range("D1").value=the context you got.

Dim TmpStr = Range("A1:B1").Value
Range("D1").value = TmpStr

Or, You can put the text in clipbox by VBA, then select the target cell where you want to paste.

0
votes

Copy the upper-left most cell in your merged range

i.e. for merged range of cells A20:C21

cells(20,1).copy

Then go paste

cells(25,1).pastespecial paste:=xlPasteValues