I am using the following code to copy and email the entire sheet.

I am trying to figure out a work around the 255 character limit. This macro
creates the sheet/email in the background. I am having trouble with how to
work in the copy of cells more than 255 characters, as I have read the only
fix is to copy the original cell/cells into the new sheet.

Thanks in advance.

----------------Code----------------
Sub Mail_ActiveSheet()
Dim wb As Workbook
On Error GoTo ErrorHandler
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
With wb
' Names the worksheet the same as activesheet
.SaveAs ActiveSheet.Name & ".xls"
' Next line has "" which is a blank email. Allows you to send to whom you
want.
.SendMail "", _
ActiveSheet.Name
' Above line is the subject line "This is the Subject line" 'Type the
subject in the previous quotes
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
ErrorHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Perhaps something like this...

Sub Mail_ActiveSheet()
Dim wb As Workbook
dim wks as worksheet
On Error GoTo ErrorHandler
Application.EnableEvents = False
Application.ScreenUpdating = False
set wks = activesheet
wks.Copy
Set wb = ActiveWorkbook
With wb
wks.cells.copy Destination:= .activesheet.cells

' Names the worksheet the same as activesheet
.SaveAs ActiveSheet.Name & ".xls"
' Next line has "" which is a blank email. Allows you to send to whom you
want.
.SendMail "", _
ActiveSheet.Name
' Above line is the subject line "This is the Subject line" 'Type the
subject in the previous quotes
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
ErrorHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
--
HTH...

Jim Thomlinson


"Judsen Jones" wrote:

> I am using the following code to copy and email the entire sheet.
>
> I am trying to figure out a work around the 255 character limit. This macro
> creates the sheet/email in the background. I am having trouble with how to
> work in the copy of cells more than 255 characters, as I have read the only
> fix is to copy the original cell/cells into the new sheet.
>
> Thanks in advance.
>
> ----------------Code----------------
> Sub Mail_ActiveSheet()
> Dim wb As Workbook
> On Error GoTo ErrorHandler
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> ActiveSheet.Copy
> Set wb = ActiveWorkbook
> With wb
> ' Names the worksheet the same as activesheet
> .SaveAs ActiveSheet.Name & ".xls"
> ' Next line has "" which is a blank email. Allows you to send to whom you
> want.
> .SendMail "", _
> ActiveSheet.Name
> ' Above line is the subject line "This is the Subject line" 'Type the
> subject in the previous quotes
> .ChangeFileAccess xlReadOnly
> Kill .FullName
> .Close False
> End With
> ErrorHandler:
> Application.ScreenUpdating = True
> Application.EnableEvents = True
> End Sub

Sub test()
Dim wsSource As Worksheet
Dim wb As Workbook
Dim wsDest As Worksheet
Dim nSiNW As Long

Set wsSource = ActiveWorkbook.ActiveSheet
nSiNW = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wb = Workbooks.Add
Application.SheetsInNewWorkbook = nSiNW
Set wsDest = wb.Worksheets(1)

wsSource.Cells.Copy wsDest.Cells

' wb.SaveAs etc....

End Sub

Regards,
Peter T

"Judsen Jones" <(E-Mail Removed)> wrote in message
news9D09B27-D0FC-4692-B233-(E-Mail Removed)...
> I am using the following code to copy and email the entire sheet.
>
> I am trying to figure out a work around the 255 character limit. This
macro
> creates the sheet/email in the background. I am having trouble with how to
> work in the copy of cells more than 255 characters, as I have read the
only
> fix is to copy the original cell/cells into the new sheet.
>
> Thanks in advance.
>
> ----------------Code----------------
> Sub Mail_ActiveSheet()
> Dim wb As Workbook
> On Error GoTo ErrorHandler
> Application.EnableEvents = False
> Application.ScreenUpdating = False
> ActiveSheet.Copy
> Set wb = ActiveWorkbook
> With wb
> ' Names the worksheet the same as activesheet
> .SaveAs ActiveSheet.Name & ".xls"
> ' Next line has "" which is a blank email. Allows you to send to whom you
> want.
> .SendMail "", _
> ActiveSheet.Name
> ' Above line is the subject line "This is the Subject line" 'Type the
> subject in the previous quotes
> .ChangeFileAccess xlReadOnly
> Kill .FullName
> .Close False
> End With
> ErrorHandler:
> Application.ScreenUpdating = True
> Application.EnableEvents = True
> End Sub