Format Copy to Worksheet

J

Jenny B.

Hello,

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
 
Top