Hi All,

I have a routine I'm using that takes data from a current workbook worksheet
and saves it to a new single worksheet at a noted location.

Here's my problem. I have a cell where I have the user enter text and
sometimes it's over the amount that Excel allows copied to an unformatted
worksheet (tries to truncate after a certain amount of words). I've avoided
this on other documents by coping to a workbook sheet thats aleady
preformatted "General", but I'm looking for a work around when copying to a
new unformatted single worksheet.

Is there a way add a piece of code to the below to call attention to that
particular cell range and change the format to €œGeneral€� so the text doesnt
truncate? The cell that has the text will be F10 in each doument.

Thanks in advance - Jenny B.

Sub Finalstop()
Dim myPath As String
Dim nRng As Range
Dim fName As String
Set nRng = Range("F64")

ActiveSheet.Copy

Call DeleteAllCode
Call UseBreakLink
ActiveSheet.Shapes("Send").Visible = False
myPath = "I:\DM\PM\Operations\B55 Docs\Checklist\"
fName = nRng.Value & ".xls"
ActiveWorkbook.SaveAs filename:= _
myPath & fName, FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Call TransfertoLog
Call CLEAR
ActiveSheet.Visible = False

End Sub

It's not the formatting that causes excel to truncate the long text. That's
just the way excel works.

Copy the sheet and go back and copy|paste the cells into the new sheet.

In code:

Dim ActSheet as worksheet
dim NewSheet as worksheet

set actsheet = activesheet

actsheet.copy 'to a new workbook with long strings truncated.

set newsheet = activesheet

actsheet.cells.copy _
destination:=newsheet.range("A1")

newsheet.parent.saveas filename:=....




Jenny B. wrote:

Hi All,

I have a routine I'm using that takes data from a current workbook worksheet
and saves it to a new single worksheet at a noted location.

Here's my problem. I have a cell where I have the user enter text and
sometimes it's over the amount that Excel allows copied to an unformatted
worksheet (tries to truncate after a certain amount of words). I've avoided
this on other documents by coping to a workbook sheet thats aleady
preformatted "General", but I'm looking for a work around when copying to a
new unformatted single worksheet.

Is there a way add a piece of code to the below to call attention to that
particular cell range and change the format to €œGeneral€� so the text doesnt
truncate? The cell that has the text will be F10 in each doument.

Thanks in advance - Jenny B.

Sub Finalstop()
Dim myPath As String
Dim nRng As Range
Dim fName As String
Set nRng = Range("F64")

ActiveSheet.Copy

Call DeleteAllCode
Call UseBreakLink
ActiveSheet.Shapes("Send").Visible = False
myPath = "I:\DM\PM\Operations\B55 Docs\Checklist\"
fName = nRng.Value & ".xls"
ActiveWorkbook.SaveAs filename:= _
myPath & fName, FileFormat:= _
xlNormal, Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
Call TransfertoLog
Call CLEAR
ActiveSheet.Visible = False

End Sub

--

Dave Peterson