Copy Cells with More than 255 Characters
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 |
Copy Cells with More than 255 Characters
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 |
Copy Cells with More than 255 Characters
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 |
Copy Cells with More than 255 Characters
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 |
All times are GMT +1. The time now is 06:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com