Copy sheet into another workbook-part of text being deleted in 1 cell. 1

[phweston]

I am working this macro that copies several sheets into another workbook.

One of the cells has text that keeps getting cut off. I checked the # of characters in the original cell, and it totalled around 472 characters total.

Here is part of the code:

vMacroBook = ActiveWorkbook.Name
strPath = Range("Path_Save").Text
ThisWorkbook.Worksheets("Industry").Copy
NewBookName = ActiveWorkbook.Name
ThisWorkbook.Worksheets("Summary").Copy Before:=Workbooks(NewBookName).Sheets(1)



Any thoughts or suggestions as too how to deal with this one cell where half of the text in one cell gets cut off?

 

[Gavona]

If you try doing the operation manually you will get an error message that explains the issue - copy worksheet can't deal with more than 255 characters in a cell.
The solution is either to use move instead (easiest) or, after the copy worksheet operation select all cells in the original worksheet and copy them to the new one.

Annoying isn't it?

Thanks,

Gavin

 

[phweston]

Thanks! I ended up adding another line to the spreadsheet to add additional text. I also added off to the side an If Statement which flags anything over 255 characters, and built something into my code that stops the user from going ahead with the copying until the # of characters is <= 255

Again thanks!

 

[Gavona]

Surely moving the sheet would be less hassle (and more robust if new rows get added etc.? You just need to save the workbook first, move the sheet(s), close the original workbook without saving (as it is now missing some sheets).

Thanks,

Gavin