Copy sheet--Some cells more than 255 characters

[Judsen Jones]

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

 

[Jim Thomlinson]

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

 

[Peter T]

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