Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
paste values
hi, the below code works great at sending the sheets in select but id lik them to be copied accross to the new book as paste values & formats no with links to other books as this is causing #value error's even when select dont update Can anyone help? 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-mai message. Column A:C enter information for the first mail and you may use column 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 Exi 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 Su -- ceem ----------------------------------------------------------------------- ceemo's Profile: http://www.excelforum.com/member.php...fo&userid=1065 View this thread: http://www.excelforum.com/showthread.php?threadid=48289 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
paste values
any able to help with my query -- ceem ----------------------------------------------------------------------- ceemo's Profile: http://www.excelforum.com/member.php...fo&userid=1065 View this thread: http://www.excelforum.com/showthread.php?threadid=48289 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
paste values
There must be an expert out there that can help? -- ceemo ------------------------------------------------------------------------ ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650 View this thread: http://www.excelforum.com/showthread...hreadid=482898 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find values in multiple cells and paste row values | Excel Discussion (Misc queries) | |||
can you change the default paste method? (paste values) | Excel Discussion (Misc queries) | |||
Paste Values | Excel Discussion (Misc queries) | |||
Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
How do i compare values from two sheet and copy & paste if values match? | Excel Programming |