Excel+Not Enough Memory..

[Olly]

I've got a 2 sheet, 4mb workbook-sheet 2 containing the
raw product data, and sheet 1 acting as a template as
such, whereby a user enters a product code, and it looksup
the relevant info from sheet 2.

Problem..I have some VBA code incorporated into a control
box button which, i had hoped, would, on clicking, would
simply do effectively a paste special of sheet 1 into a
new workbook, temporarily save the workbook, email it off
to a pre-specified email address, and then delete the temp
workbook. Here is the code i used:-

Sub Mail_ActiveSheet()
Dim strDate As String

Application.ScreenUpdating = False
strDate = Format(Date, "dd-mm-yy") & " " & Format
(Time, "h-mm-ss")
FName$ = "Confirmation - " & strDate
ThisWorkbook.ActiveSheet.Copy
With ActiveWorkbook
With .ActiveSheet.UsedRange
.Copy
.PasteSpecial xlPasteValues
End With
.SaveAs Filename:="c:\" & FName$ & ".xls"
.SendMail "(e-mail address removed)", "Confirmation
" & strDate
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
End Sub

The problem is this-i have 48,000 rows of 4 colums of raw
data in sheet 2. But when i press my button, i get the
error message "Microsoft Excel Error-Not enough memory".

Now the lookups work fine and everything, and if i email
the whole workbook, it works fine, its just when i try to
email the first sheet i get this message, what am i doing
wrong??

Any help greatly appreciated
cheers
olly

 

[Ron de Bruin]

Hi Olly

Try this on the sheet

Sub Mail_ActiveSheet1()
Dim strDate As String
ActiveSheet.Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False

strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.SendMail "(e-mail address removed)", _
"This is the Subject line"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

 

[olly]

-----Original Message-----
Hi Olly

Try this on the sheet

Sub Mail_ActiveSheet1()
Dim strDate As String
ActiveSheet.Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False

strDate = Format(Date, "dd-mm-yy") & " " & Format (Time, "h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.SendMail "(e-mail address removed)", _
"This is the Subject line"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

Hi Ron
Cheers for that, but now getting this message
Run Time Error 1004
Select method of range class failed

any ideas ?
cheers
olly

 

[olly]

-----Original Message-----
Hi Olly

Try this on the sheet

Sub Mail_ActiveSheet1()
Dim strDate As String
ActiveSheet.Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Application.CutCopyMode = False

strDate = Format(Date, "dd-mm-yy") & " " & Format (Time, "h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strDate & ".xls"
ActiveWorkbook.SendMail "(e-mail address removed)", _
"This is the Subject line"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub

and now its back to out of memory error ..
hmmm
olly