Save macro Truncating text

[Brian]

I have a worksheet that I merged 7 cells wide x 19 cells high. I have
the proprieties set to "Wrap Text". I use this box for a field report.

I am using This Macro to save the worksheet to a new workbook.

Sub savesheet()


Application.ScreenUpdating = True
Dim wb As Workbook
ActiveSheet.Copy

Set wb = ActiveWorkbook
Application.ScreenUpdating = True
sFilename = "C:\TimeSheets\Week Ending " &
Format(Range("h12").Value, "mm-dd-yy Job# ") &
ActiveSheet.Range("h2").Value & " " & Range("b5").Value
ans = MsgBox("Save file as " & sFilename)

If ans = vbOK Then
With wb

ActiveSheet.Shapes("Button 2").Select
Selection.Delete

.SaveAs sFilename
.Close False
End With
End If
End Sub


After I run this macro the new sheet the text in this Field Report Box
is truncated at 255 charaters. Help!

 

[Tom Ogilvy]

This happens when you copy a sheet. Just add a command to rewrite the text
in the copied sheet. Assume the upper left corner of your merged cell is
cell B9

Dim wb As Workbook
Dim sh as Wroksheet
set sh = Activesheet
sh.Copy
activesheet.Range("B9").value = sh.Range("B9").Value
Set wb = ActiveWorkbook

 

[Brian]

That kind of worked. My code is saving the workbook w/ the correct
name and closing the workbook before it rewrites the text. It creates
Book# with the correct text.
New Code:

Sub savesheet()


Application.ScreenUpdating = True
Dim wb As Workbook
Dim sh As Worksheet
Set sh = ActiveSheet
sh.Copy
ActiveSheet.Range("B25").Value = sh.Range("B25").Value
ActiveSheet.Copy

Set wb = ActiveWorkbook
Application.ScreenUpdating = True
sFilename = "C:\TimeSheets\Week Ending " &
Format(Range("h12").Value, "mm-dd-yy Job# ") &
ActiveSheet.Range("h2").Value & " " & Range("b5").Value
ans = MsgBox("Save file as " & sFilename)

If ans = vbOK Then
With wb
ActiveSheet.Shapes("Button 2").Select
Selection.Delete


.SaveAs sFilename
.Close False
End With
End If
End Sub

 

[Tom Ogilvy]

this line isn't necessary:

ActiveSheet.Copy

the sh.copy replaced that. sorry you didn't understand that.

 

[Brian]

Thanks,
It works great now.

this line isn't necessary:

ActiveSheet.Copy

the sh.copy replaced that. sorry you didn't understand that.