Save macro Truncating text

Brian · 06-13-2006, 09:10 AM

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 · 06-13-2006, 09:40 AM

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

--
Regards,
Tom Ogilvy

"Brian" wrote:

> 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!
>
>

Brian · 06-13-2006, 10:30 AM

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 wrote:
> 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
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Brian" wrote:
>
> > 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 · 06-13-2006, 11:20 AM

this line isn't necessary:

ActiveSheet.Copy

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

--
Regards,
Tom Ogilvy

"Brian" wrote:

> 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 wrote:
> > 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
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Brian" wrote:
> >
> > > 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!
> > >
> > >
>
>

Brian · 06-13-2006, 11:50 AM

Thanks,
It works great now.

Tom Ogilvy wrote:
> this line isn't necessary:
>
> ActiveSheet.Copy
>
> the sh.copy replaced that. sorry you didn't understand that.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Brian" wrote:
>
> > 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 wrote:
> > > 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
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > > "Brian" wrote:
> > >
> > > > 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!
> > > >
> > > >
> >
> >