Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Handy Indeed
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 -- ceemo ------------------------------------------------------------------------ ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650 View this thread: http://www.excelforum.com/showthread...hreadid=504524 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Handy Indeed
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. --- 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 "ceemo" wrote in message ... 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 -- ceemo ------------------------------------------------------------------------ ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650 View this thread: http://www.excelforum.com/showthread...hreadid=504524 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Handy Indeed
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? -- ceemo ------------------------------------------------------------------------ ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650 View this thread: http://www.excelforum.com/showthread...hreadid=504524 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Handy Indeed
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 "ceemo" wrote in ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Very Handy Indeed
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 "David McRitchie" wrote in message ... 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 "ceemo" wrote in ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|