Circumventing silly 255 char limit when copying entire sheet

TryingMyBest

New Member
Joined
Dec 9, 2005
Messages
4
I am having mucho problems because of Excel's 255 character limit when copying an entire spreadsheet to a new workbook.

Does anyone have ideas/code to circumvent it?

In a nutshell, I copy a "data entry" page onto a “report” page within a workbook. The entire “report” page is then copied and moved into a new workbook. That allows me to have a stand alone, exported file outside of my main data entry tool. When you copy an entire page to a new workbook, Excel imposes a 255 character limit. Thus, I see truncated responses on the exported spreadsheet only.

Thanks!!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

LxQ

Well-known Member
Joined
Feb 9, 2006
Messages
619
how about just moving the worksheet instead of copying? and check the "create a copy" box when you move it..
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Or maybe,

save the file as...new filename..
delete all OTHER sheets.
 

TryingMyBest

New Member
Joined
Dec 9, 2005
Messages
4
Thanks for the ideas. 2 thoughts in return.

I'm copying the sheet via a macro. So I can't check the "create a copy" box in the move spreadsheet form. A snipett of that macro is below.

Sheets("entry").Select
Range("B82:D82").Copy
Sheets("report").Select
Range("D85").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False

Sheets("report").Range("A1").Select

Sheets("entry").Select
Sheets("report").Copy

I'm pasting values from my "entry" sheet into a "report" sheet and then copying the report sheet so that it becomes a stand alone file outside of the main tool.

Is there code that will allow me to paste values into the newly created workbook from my original workbook?? That could be a work around ...
 

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
Hi
try the following codes
Code:
Sheets("entry").Range("B82:D82").Copy 
Workbooks("newbook.xls").worksheets("sheet1").Range("D85").Select 
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False
Ravi
 

TryingMyBest

New Member
Joined
Dec 9, 2005
Messages
4
Thank you very much for your insight. I truly appreciate it. However, I'm now wondering if you can go back and forth several times between workbooks using VBA.

I'm copying a whole shed load of formats and data points from my "report" page to the newly created workbook. It seems most efficient to just copy the entire page (data and formats and all that jazz) and then copy and paste over individual cells that I know are likely to be cut off due to the character limit issue you encounter when copying entire sheets.

After I've copied the entire sheet and thus created a new workbook, is there a way to indicate in VBA that the Active Workbook should be the original in order to copy the cells needed and then select the newly created workbook housing the "report" page in order to paste those cells??
 

grd

Board Regular
Joined
Oct 19, 2005
Messages
210
Can anyone advise how to locate the cells which have in excess of 255 characters?

I receive the message when copying the sheet to another worksheet by right clicking the sheet tab but can not locate a cell with that many characters.
 

TryingMyBest

New Member
Joined
Dec 9, 2005
Messages
4
I use a very refined technique commonly known as interocular estimation -- I have to eye ball which cells appear to be cut off. :p I'm sorry, but I'm not aware of a forumla that will return cells that exceed the character limit.
 

LxQ

Well-known Member
Joined
Feb 9, 2006
Messages
619
Can anyone advise how to locate the cells which have in excess of 255 characters?

Well, the LEN function counts the number of characters, so depending how much space you have and how you want to formulate this, you may use that..

Let's say you go to the last line and write something like
=SUMPRODUCT(--(LEN(A1:A:65500)>255))

This will count how many cells you got in that column that exceed 255 characters.. if you do the same for rows, you may get a cross reference.. if there are many of them, it's messy.. so maybe some LOOKUP function might help..
 

grd

Board Regular
Joined
Oct 19, 2005
Messages
210
I use a very refined technique commonly known as interocular estimation -- I have to eye ball which cells appear to be cut off. :p I'm sorry, but I'm not aware of a forumla that will return cells that exceed the character limit.
Not so easy as I suspect the problem must be in a formula - there are no text cells with anywhere near that number of characters and, in any case, if it is text I'm not too worried.

However, thinking about it, if a formula could be more than 255 and it becomes truncated I guess I would get an error message in the new worksheet.
 

Forum statistics

Threads
1,181,371
Messages
5,929,561
Members
436,677
Latest member
CathalP1992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top