Hello Ron From Steved

this part of below Worksheets(1).Select gives me a run error 1004

Ron The macro copies the sheet too a new workbook, I am fine with that
but I do not want my origional workbook to copy, past special values, this is
sugguesting to me that i've not quiet got this correct.

In my mind I believe it should copy the worksheet named "To Depots" to a new
workbook which it is doing, question at this point can the macro then copy
past special value.

I thankyou

Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False




Sub Mail_Todepot()
Dim wb As Workbook
Dim strdate As String

strdate = Format(Now, "dd-mm-yy")

Application.ScreenUpdating = False

Sheets("To Depots").Copy

Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False

Set wb = ActiveWorkbook
With wb

.SendMail ", _
"Kilometres Per Bus"

.Close False

End With

Application.ScreenUpdating = True
End Sub







"Ron de Bruin" wrote:

Hi Steve

First you have two copy lines

Sheets("To Depot").Copy
ActiveSheet.Copy

remove the second one


.SaveAs "C:/To Depots/Kilometres.xls"

",_

Must be

.SendMail ", _
"Kilometres Per Bus"

note the space before the _


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


"Steved" wrote in message ...
Hello from Steved

On the below line I'm receivinfg a compile error Syntax error

",_

Would somebody be kind enough to point out to me please what is it that I'm
not getting right.

Thankyou.

Sub Mail_Todepot()
Dim wb As Workbook
Dim strdate As String

strdate = Format(Now, "dd-mm-yy")

Application.ScreenUpdating = False

Sheets("To Depot").Copy
ActiveSheet.Copy
Cells.Copy
Cells.PasteSpecial xlPasteValues
Cells(1).Select
Worksheets(1).Select
Application.CutCopyMode = False

Set wb = ActiveWorkbook
With wb

.SaveAs "C:/To Depots/Kilometres.xls"

",_

"Kilometres Per Bus"

.ChangeFileAccessxlReadOnly
Kill.FullName

.Close False

End With

Application.ScreenUpdating = True
End Sub