Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy all but last 9 characters of a cell | Excel Discussion (Misc queries) | |||
Copy string of characters | Excel Discussion (Misc queries) | |||
How do I copy more than 255 characters per cell? | Excel Discussion (Misc queries) | |||
copy select characters from specified cells. | Excel Worksheet Functions | |||
Copy data where some cells exceed 256 characters | Excel Programming |