Search code examples
excelvbatextformattingcopy-paste

VBA excel paste as text

I need to paste numbers as text. It is problematic due to lots of zeros in front (but I need them there). First snippet, does not work (I found it on the internet).

Worksheets("B").Range("k7:k7").PasteSpecial Format:=”Text”, 
Link:=False, DisplayAsIcon:=False

The other I recorded, but it not always work. 

PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, 
SkipBlanks:=False, Transpose:=False

Could someone suggest me a better solution, please?

Solution

  • You need to format the cells/column to 'Text' format then paste the numbers to preserve the leading zeros. If it still doesn't work, try to paste the numbers to Notepad first and then copy all numbers in Notepad and paste it back to the cells/column now formatted as 'Text'. Hope it helps.