Very Handy Indeed

[ceemo]

I got this brillant piece of code from exceltip.com the only problem is,
if the page has formula's the formulas get copied into the new book but
i'd like it to just copy the value's. Please can someone show me hiw to
do this with this particular piece of code?

Please help this code will be very handy !!





» Mail sheet(s) to one or more people using VBA in Microsoft Excel
VBA macro tip contributed by Ron de Bruin, Microsoft MVP - Excel
CATEGORY: Mail - Send and Receive in VBA

VERSIONS: All Microsoft Excel Versions
Add new sheet, change the sheet name to mail.
Every mail you want to send will use 3 columns.

1. in column A - enter sheet or sheets name you want to send.
2. in column B - enter E-mail address.
3. in column C - the subject title appears at the top of the E-mail
message.

Column A:C enter information for the first mail and you may use columns
D:F for the second one.
you can send 85 different E-mails this way (85*3 = 255 columns).

Sub Mail_sheets()
Dim MyArr As Variant
Dim last As Long
Dim shname As Long
Dim a As Integer
Dim Arr() As String
Dim N As Integer
Dim strdate As String
For a = 1 To 253 Step 3
If ThisWorkbook.Sheets("mail").Cells(1, a).Value = "" Then Exit
Sub
Application.ScreenUpdating = False
last = ThisWorkbook.Sheets("mail").Cells(Rows.Count,
a).End(xlUp).Row
N = 0
For shname = 1 To last
N = N + 1
ReDim Preserve Arr(1 To N)
Arr(N) = ThisWorkbook.Sheets("mail").Cells(shname,
a).Value
Next shname
ThisWorkbook.Worksheets(Arr).Copy
strdate = Format(Date, "dd-mm-yy") & " " & Format(Time,
"h-mm-ss")
ActiveWorkbook.SaveAs "Part of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
With ThisWorkbook.Sheets("mail")
MyArr = .Range(.Cells(1, a + 1), .Cells(Rows.Count, a +
1).End(xlUp))
End With
ActiveWorkbook.SendMail MyArr,
ThisWorkbook.Sheets("mail").Cells(1, a + 2).Value
ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill ActiveWorkbook.FullName
ActiveWorkbook.Close False
Application.ScreenUpdating = True
Next a
End Sub

 

[David McRitchie]

Just one page and already copied
select all cells (Ctrl+A, or in Excel 2003 Ctrl+Shift+A)
Copy (ctrl+C)
Paste Special (Edit, Paste Special Values or shortcut Ctrl+Shift+V)

If you want to copy an entire workbook as values to another workbook
you would need a macro.

 

[ceemo]

thanx for your reply but im not really looking for the shortcuts im
looking for the code to be adjusted to auto paste the value's for me
rather than the formula's.

There must be someone on here with the expertise to help me perform
this action?

 

[David McRitchie]

Sorry I missed that there was code included.
Perhaps this page at Ron's site
http://www.rondebruin.nl/mail/folder1/mail4.htm
which has paste and saveas values

 

[Ron de Bruin]

Hi ceemo and David

I create a template from this code that have the option to make values
http://www.rondebruin.nl/mail/templates.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl

Sorry I missed that there was code included.
Perhaps this page at Ron's site
http://www.rondebruin.nl/mail/folder1/mail4.htm
which has paste and saveas values
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

thanx for your reply but im not really looking for the shortcuts im
looking for the code to be adjusted to auto paste the value's for me
rather than the formula's.

There must be someone on here with the expertise to help me perform
this action?