|
Email Syntax error
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 |
Email Syntax error
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 |
Email Syntax error
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 |
Email Syntax error
Hi Steve
If you use this Sheets("To Depots").Copy It will create a new workbook with this sheet The one sheet workbook is the active one now so you can use this to make values Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Application.CutCopyMode = False Have you try it ??? -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... 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 |
Email Syntax error
Hello Ron from Steved
The below is what I am running two issues 1 when I run it I get a error 400 2 it values the sheet that it is copying from then creates a new workbook. 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 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 If you use this Sheets("To Depots").Copy It will create a new workbook with this sheet The one sheet workbook is the active one now so you can use this to make values Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Application.CutCopyMode = False Have you try it ??? -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... 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 |
Email Syntax error
Send me the workbook private Steve
I will look at your problem tomorrow after work -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved The below is what I am running two issues 1 when I run it I get a error 400 2 it values the sheet that it is copying from then creates a new workbook. 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 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 If you use this Sheets("To Depots").Copy It will create a new workbook with this sheet The one sheet workbook is the active one now so you can use this to make values Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Application.CutCopyMode = False Have you try it ??? -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... 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 |
Email Syntax error
Thanks Ron
Before I do what you requested I'll going to try an idea. because this is part of a workbook with many sheets I am going to put it in a file off its own then work through What I am not doing correctly. Cheers. "Ron de Bruin" wrote: Send me the workbook private Steve I will look at your problem tomorrow after work -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved The below is what I am running two issues 1 when I run it I get a error 400 2 it values the sheet that it is copying from then creates a new workbook. 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 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 If you use this Sheets("To Depots").Copy It will create a new workbook with this sheet The one sheet workbook is the active one now so you can use this to make values Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Application.CutCopyMode = False Have you try it ??? -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... 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 |
Email Syntax error
Hello Ron from Steved
Ron I seperated out the worksheet to depots to a individual workbook I then ran the macro and had no issues as you have designed it to do. So the issue I have to fiquire is why will it not operate on multiple sheets. I'll work on it hopping I can workout what I've done different. Thankyou. "Steved" wrote: Thanks Ron Before I do what you requested I'll going to try an idea. because this is part of a workbook with many sheets I am going to put it in a file off its own then work through What I am not doing correctly. Cheers. "Ron de Bruin" wrote: Send me the workbook private Steve I will look at your problem tomorrow after work -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved The below is what I am running two issues 1 when I run it I get a error 400 2 it values the sheet that it is copying from then creates a new workbook. 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 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 If you use this Sheets("To Depots").Copy It will create a new workbook with this sheet The one sheet workbook is the active one now so you can use this to make values Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Application.CutCopyMode = False Have you try it ??? -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... 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 |
Email Syntax error
Hello Ron from Steved
oops I forgot what you advised me to do, and that is make sure the macro is in the right module. Thanks Ron as soon as you said that you would take a look for me I knew in my mind I better trace my steps and work it out for myself. I have other situations where your good macro will help. Have a nice day. and Once again thanks. "Steved" wrote: Hello Ron from Steved Ron I seperated out the worksheet to depots to a individual workbook I then ran the macro and had no issues as you have designed it to do. So the issue I have to fiquire is why will it not operate on multiple sheets. I'll work on it hopping I can workout what I've done different. Thankyou. "Steved" wrote: Thanks Ron Before I do what you requested I'll going to try an idea. because this is part of a workbook with many sheets I am going to put it in a file off its own then work through What I am not doing correctly. Cheers. "Ron de Bruin" wrote: Send me the workbook private Steve I will look at your problem tomorrow after work -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... Hello Ron from Steved The below is what I am running two issues 1 when I run it I get a error 400 2 it values the sheet that it is copying from then creates a new workbook. 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 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 If you use this Sheets("To Depots").Copy It will create a new workbook with this sheet The one sheet workbook is the active one now so you can use this to make values Cells.Copy Cells.PasteSpecial xlPasteValues Cells(1).Select Application.CutCopyMode = False Have you try it ??? -- Regards Ron de Bruin http://www.rondebruin.nl "Steved" wrote in message ... 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 |
| All times are GMT +1. The time now is 07:17 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com