PDA

View Full Version : Problem with .PasteSpecial (xlPasteValues)



ccPaulCox
09-05-2007, 07:44 AM
When I use PasteSpecial (xlPasteValues) in a macro to copy the contents of a sheet without the underlying formulas, Excel is truncating cells with more than 255 characters. The problem is that I have some large text cells with up to 500 characters in them and even these are being limited to 255.

If I perform this process manually using copy, PasteSpecial, Values - it works fine without truncating the text.

Any suggestions?

Thanks (this is my first time on VBA Express so I apologise in advance for my faltering ways!)

cc

rory
09-05-2007, 07:49 AM
Which version of Excel are you using, and are you copying between different Excel sessions?

ccPaulCox
09-05-2007, 07:53 AM
Excel 2003 sp2

I'm only attempting to paste a sheet containing formulas directly back onto itself using ValueCopy in order to remove the formulas.

Thanks

rory
09-05-2007, 07:55 AM
Curious. What's the exact code you are using? (I don't get that effect in XL2002)

tpoynton
09-05-2007, 04:58 PM
you could try looping through each cell in the range instead of copying it...for example



Sub killFormulas()
Dim cell As Range
For Each cell In Range(Cells(1, 3), Cells(6, 3)) 'change range to suit your needs
cell.Value = cell.Value
Next cell

End Sub

ccPaulCox
09-06-2007, 04:41 AM
Thanks - the KillFormulas worked really well, but it also revealed that the underlying cause of the text truncation was the previous step in the process. I created the work sheet by using ActiveSheet.Copy which created a replica of the current sheet but failed to copy more than 255 characters from cells with more than 255. That's why Rory couldn't replicate the problem.

Is there another way of copying the active sheet?

Thanks for the excellent help so far.

Cheers
Paul

rory
09-06-2007, 04:59 AM
Create a new blank worksheet, copy the cells from your worksheet to the new one, then do a copy->paste special - Values