Format Copy to Worksheet

[Jenny B.]

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 that’s 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 doesn’t
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]

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:=....