Excel+Not Enough Memory..
 

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 ", "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

Excel+Not Enough Memory..
 

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 ", _
"This is the Subject line"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Olly" wrote in message ...
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 ", "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

Excel+Not Enough Memory..
 

-----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 ", _
"This is the Subject line"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl


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

any ideas ?
cheers
olly

Excel+Not Enough Memory..
 

-----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 ", _
"This is the Subject line"
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl




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