Copy Cells with More than 255 Characters

[Tim Childs]

If a sheet is copied with cells having a length of more than 255
characters then a truncation error occurs on those cells.

Excel prompts to use a copying of cells rahter than whole sheets. This
is fine if the cells in the original sheet have not been merged.
Unfortunately, my sheet includes extensive use of long text strings in
merged cells.

Has anyone a workaround for this, please?

Any help would be a greatly appreciated

Tim

 

[Peter Grebenik]

Tim

Here is some code which seems to work for me. I would switch excel
into manual calculation before running the code.

Peter

Sub CopySelectedSheets()
Dim NoSh As Integer, SheetNames() As String, PNoSh As Integer, Nc As Integer
Dim SrcB As Workbook, DesB As Workbook

With ActiveWindow.SelectedSheets
ReDim SheetNames(1 To .Count)
For Nc = 1 To .Count
SheetNames(Nc) = .Item(Nc).Name
Next
End With

NoSh = Nc - 1
PNoSh = Application.SheetsInNewWorkbook

Application.SheetsInNewWorkbook = NoSh
Set SrcB = ActiveWorkbook
Set DesB = Workbooks.Add
For Nc = 1 To NoSh
SrcB.Sheets(SheetNames(Nc)).Cells.Copy _
Destination:=DesB.Sheets(Nc).Cells
DesB.Sheets(Nc).Name = SheetNames(Nc)
Next

Application.SheetsInNewWorkbook = PNoSh

End Sub

 

[Peter Grebenik]

Tim

Here is some code which seems to work for me. I would switch excel
into manual calculation before running the code.

Sub CopySelectedSheets()
Dim NoSh As Integer, SheetNames() As String, PNoSh As Integer, Nc
As Integer
Dim SrcB As Workbook, DesB As Workbook

With ActiveWindow.SelectedSheets
ReDim SheetNames(1 To .Count)
For Nc = 1 To .Count
SheetNames(Nc) = .Item(Nc).Name
Next
End With

NoSh = Nc - 1
PNoSh = Application.SheetsInNewWorkbook

Application.SheetsInNewWorkbook = NoSh
Set SrcB = ActiveWorkbook
Set DesB = Workbooks.Add
For Nc = 1 To NoSh
SrcB.Sheets(SheetNames(Nc)).Cells.Copy _
Destination:=DesB.Sheets(Nc).Cells
DesB.Sheets(Nc).Name = SheetNames(Nc)
Next

Application.SheetsInNewWorkbook = PNoSh

End Sub

 

[Tim Childs]

Peter

Many thanks for coming back on this.

The one thing that is then missing from the copy is the properties of
the sheet itself e.g the print pagesetup.

Best wishes

Tim