copying cells with more then 255 characters to new workbook

[Tucker]

I'm copying a sheet to a new workbook and saving it.
There are 2 merged cells (B8 and B24) that contain uip to 1000 characters.
My sheet copies and saves fine (including removing buttons, keeping logos,
pasting values, protecting the sheet and removing copied VB code).

Just these two cells do not copy the full content and cut off at 255
characters. The text is visable on the orriginal (format is set to general)
and the original work books stays open at all times - (I know both these can
be possible causes)

Full code pasted below - Any help would be appreciated.

Sub Make_New_Book()

Dim Sourcewb As Workbook
Dim Destwb As Workbook
Dim sh As Worksheet

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Copy every sheet from the workbook with this macro
Set Sourcewb = ThisWorkbook

Sheets("Monitoring Template").Copy
'Set Destwb to the new workbook
Set Destwb = ActiveWorkbook

'Change all cells in the worksheet to values if you want
If Destwb.Sheets(1).ProtectContents = False Then
With Destwb.Sheets(1).UsedRange
.Cells.Copy
.Cells.PasteSpecial xlPasteValues
.Cells(1).Select
End With
Application.CutCopyMode = False
End If

For Each sShape In ActiveSheet.Shapes
If sShape.Name <> "LOGO" Then sShape.Delete
Next sShape


Destwb.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.DeleteLines 1,
Destwb.VBProject.VBComponents(ActiveSheet.CodeName).CodeModule.CountOfLines

'Save the new workbook and close it
ActiveSheet.Protect ("password")
ActiveWorkbook.SaveAs Filename:=Range("B31").Value
ActiveWorkbook.Close

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[Jacob Skaria]

This is a limitation. Refer the below link

http://office.microsoft.com/en-us/excel/HP051992911033.aspx

 

[Tucker]

Thanks.

I'm 99.9% sure it can be done. My workbook already shows more then 255
characters and manually copy/paste to a new workbook I can transfer all 1000
characters.

I just don't know how to do it through VB code. I think I need to copy and
paste these cells on their own but I can't woork out where in the code to put
it or what code to use ( getting myself confused with having 2 workbooks open
etc)

Hopefully there is a way.

 

[Jacob Skaria]

The below link refers to excel 2003....

 

[Dave Peterson]

After you copy the sheet (and the values are truncated), just go back and copy
the cells from the old sheet and paste to the new.

Dim wks as worksheet
dim newwks as worksheet

set wks = workSheets("Monitoring Template")

wks.copy 'to a new workbook
set newwks = activesheet 'that new sheet in the new workbook.

wks.cells.copy _
destination:=newwks.range("a1")

========
The real problem is those merged cells. You may want to do some testing first.