Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
Hi,
I have a workbook that consists of two worksheets, one called "rotas" and one called "dates". The rota worksheet has a form associated with it which, when completed inserts certain data into the worksheet. The worksheet has a button called "Send" and the macro associated with the button is shown below. This saves the worksheet to my Y drive and emails a copy to various people. All of this works fine. In column A of the "dates" worksheet there are a series of dates the earliest being in cell A1 and subsequently in A2, A3 etc each date being 7 days after the one above. What I would like to do is automatically delete row 1 in the "dates" worksheet each time the form runs the macro below. Private Sub CommandButtonSend_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs Fpath & Format(Range("J4"), "dd-mmm-yy") & ".xls" .SendMail ", Format(Range("J4"), "dd-mmm-yy") .SendMail ", Format(Range("J4"), "dd-mmm-yy") .Close False Range("I8:AA9,I12:AA13,I16:AA18,I25:U25,J4,I24:U24 ").Select Selection = Clear End With Application.ScreenUpdating = True Unload Me I have a macro that, when run in isolation, will do exactly this - the macro is: Sheets("Dates").Select Range("A1").Select Selection.ClearContents Rows("1:1").Select Selection.Delete Shift:=xlUp Sheets("Rotas").Select However when I try and slot this into the first macro either the date in cell A1 is deleted, but not Row A or the date in cell A1 is deleted and not Row A and a number of dates are spread about the "dates" worksheet - specifically, if A1 contained 15 May, then J4 & I24 will contain 16 May, L24 17 May, O24 18 May, R24 19 May & U24 20 May. I've obviously got something wrong somewhere, but just cannot work it out - any suggestions/hints/help with this would be very much appreciated. -- Cheers Peter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
On Thu, 5 May 2005 13:27:19 -0500, "Don Guillett"
wrote: try sheets("dates").rows(1).delete I get an error message when I try this: Run-time error '9' Subscript out of range -- Cheers Peter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
Subscript out of range means that the workbook doesn't have a worksheet named
"Dates". Is it a typo or are you on the wrong workbook? Peter wrote: On Thu, 5 May 2005 13:27:19 -0500, "Don Guillett" wrote: try sheets("dates").rows(1).delete I get an error message when I try this: Run-time error '9' Subscript out of range -- Cheers Peter -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
On Thu, 05 May 2005 17:51:15 -0500, Dave Peterson
wrote: Subscript out of range means that the workbook doesn't have a worksheet named "Dates". Is it a typo or are you on the wrong workbook? Hi Dave, Thanks for your reply. It was a typo - the sheet is named Dates, not dates. However, it still doesn't work. The macro now deletes the date in cell A1, but doesn't delete row A and the date that was in A1 is now found in cell C2 ! Perhaps I've placed the script in the wrong place. This is how my macro looks at the moment: Sub CommandButtonSnd_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False Sheets("Dates").Rows(1).Delete ActiveSheet.Copy Set wb = ActiveWorkbook With wb '.SaveAs Fpath & Format(Range("c2"), "dd-mmm-yy") & ".xls" '.SendMail Format(Range("c2"), "dd-mmm-yy") .Close False End With Application.ScreenUpdating = True Unload Me -- Cheers Peter |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
First, is this a button on a userform (designed in the VBE) or is this a
worksheet designed to look like a form? From your earlier post, it sounded like it was a worksheet--but then I see "Unload me", so I'm confused. I'm gonna guess that it's on the worksheet named Rotas and does the work against the worksheet named Dates: Option Explicit Private Sub CommandButton_Snd_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False With Sheets("dates") .Rows(1).Delete .Copy End With Set wb = ActiveWorkbook With wb .SaveAs Fpath _ & Format(.Worksheets(1).Range("c2"), "dd-mmm-yy") & ".xls" '.SendMail .Close False End With Application.ScreenUpdating = True End Sub Peter wrote: On Thu, 05 May 2005 17:51:15 -0500, Dave Peterson wrote: Subscript out of range means that the workbook doesn't have a worksheet named "Dates". Is it a typo or are you on the wrong workbook? Hi Dave, Thanks for your reply. It was a typo - the sheet is named Dates, not dates. However, it still doesn't work. The macro now deletes the date in cell A1, but doesn't delete row A and the date that was in A1 is now found in cell C2 ! Perhaps I've placed the script in the wrong place. This is how my macro looks at the moment: Sub CommandButtonSnd_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False Sheets("Dates").Rows(1).Delete ActiveSheet.Copy Set wb = ActiveWorkbook With wb '.SaveAs Fpath & Format(Range("c2"), "dd-mmm-yy") & ".xls" '.SendMail Format(Range("c2"), "dd-mmm-yy") .Close False End With Application.ScreenUpdating = True Unload Me -- Cheers Peter -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
On Fri, 06 May 2005 08:04:49 -0500, Dave Peterson
wrote: First, is this a button on a userform (designed in the VBE) or is this a worksheet designed to look like a form? From your earlier post, it sounded like it was a worksheet--but then I see "Unload me", so I'm confused. I'm gonna guess that it's on the worksheet named Rotas and does the work against the worksheet named Dates: Option Explicit Private Sub CommandButton_Snd_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False With Sheets("dates") .Rows(1).Delete .Copy End With Set wb = ActiveWorkbook With wb .SaveAs Fpath _ & Format(.Worksheets(1).Range("c2"), "dd-mmm-yy") & ".xls" '.SendMail .Close False End With Application.ScreenUpdating = True End Sub Hi ADve, Thanks for your help. This is a userform and the button is contained on (in?) it. I've just tried the above and again, it deletes the date from cell A1, but does not delete Row 1. Also the deleted date now appears in cell C3. A copy of the exact script (with the email address the only thing that is altered) Private Sub CommandButtonSnd_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False With Sheets("Dates") .Rows(1).Delete ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs Fpath & Format(Range("c2"), "dd-mmm-yy") & ".xls" .SendMail ",Format(Range("c2"), "dd-mmm-yy") .Close False End With Application.ScreenUpdating = True Unload Me End With End Sub -- Cheers Peter |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
What worksheet should be copied?
If it's Dates, then this was ok. With Sheets("dates") .Rows(1).Delete .Copy End With Did you really want to copy the activesheet? I saw nothing in your code that would move the date into column C--any formulas/macros working against you? Peter wrote: On Fri, 06 May 2005 08:04:49 -0500, Dave Peterson wrote: First, is this a button on a userform (designed in the VBE) or is this a worksheet designed to look like a form? From your earlier post, it sounded like it was a worksheet--but then I see "Unload me", so I'm confused. I'm gonna guess that it's on the worksheet named Rotas and does the work against the worksheet named Dates: Option Explicit Private Sub CommandButton_Snd_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False With Sheets("dates") .Rows(1).Delete .Copy End With Set wb = ActiveWorkbook With wb .SaveAs Fpath _ & Format(.Worksheets(1).Range("c2"), "dd-mmm-yy") & ".xls" '.SendMail .Close False End With Application.ScreenUpdating = True End Sub Hi ADve, Thanks for your help. This is a userform and the button is contained on (in?) it. I've just tried the above and again, it deletes the date from cell A1, but does not delete Row 1. Also the deleted date now appears in cell C3. A copy of the exact script (with the email address the only thing that is altered) Private Sub CommandButtonSnd_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False With Sheets("Dates") .Rows(1).Delete ActiveSheet.Copy Set wb = ActiveWorkbook With wb .SaveAs Fpath & Format(Range("c2"), "dd-mmm-yy") & ".xls" .SendMail ",Format(Range("c2"), "dd-mmm-yy") .Close False End With Application.ScreenUpdating = True Unload Me End With End Sub -- Cheers Peter -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
On Fri, 06 May 2005 19:10:14 -0500, Dave Peterson
wrote: What worksheet should be copied? If it's Dates, then this was ok. With Sheets("dates") .Rows(1).Delete .Copy End With Did you really want to copy the activesheet? I saw nothing in your code that would move the date into column C--any formulas/macros working against you? Hi Dave, The workbook contains 2 worksheets, Rotas & Dates. When i open the workbook it opens the Rotas worksheet and there is a button which calls up the form. I input data into the rota worksheet by means of option buttons. I also input a date into the Rota worksheet by means of a combobox which, by means of the rowsource function, displays a series of dates that are contained in Column A of the Dates worksheet. What i am trying to achieve is the deletion of the first date in the column each time I use the workbook, as the date is never used again and it will save me having to open up the dates worksheet and manually delete the first row, so that date isn't shown in the combo box the next time I use the workbook The active sheet (always the rota worksheet) is copied, local print is made and it is emailed to about 10 people. -- Cheers Peter |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
Option Explicit
Private Sub CommandButton_Snd_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False With Sheets("dates") .Rows(1).Delete end with worksheet("Rotas").Copy Set wb = ActiveWorkbook With wb .SaveAs Fpath _ & Format(.Worksheets(1).Range("c2"), "dd-mmm-yy") & ".xls" '.SendMail .Close False End With Application.ScreenUpdating = True End Sub I don't see anything that would change the date to a different cell. Peter wrote: On Fri, 06 May 2005 19:10:14 -0500, Dave Peterson wrote: What worksheet should be copied? If it's Dates, then this was ok. With Sheets("dates") .Rows(1).Delete .Copy End With Did you really want to copy the activesheet? I saw nothing in your code that would move the date into column C--any formulas/macros working against you? Hi Dave, The workbook contains 2 worksheets, Rotas & Dates. When i open the workbook it opens the Rotas worksheet and there is a button which calls up the form. I input data into the rota worksheet by means of option buttons. I also input a date into the Rota worksheet by means of a combobox which, by means of the rowsource function, displays a series of dates that are contained in Column A of the Dates worksheet. What i am trying to achieve is the deletion of the first date in the column each time I use the workbook, as the date is never used again and it will save me having to open up the dates worksheet and manually delete the first row, so that date isn't shown in the combo box the next time I use the workbook The active sheet (always the rota worksheet) is copied, local print is made and it is emailed to about 10 people. -- Cheers Peter -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
On Sat, 07 May 2005 06:55:04 -0500, Dave Peterson
wrote: Option Explicit Private Sub CommandButton_Snd_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False With Sheets("dates") .Rows(1).Delete end with worksheet("Rotas").Copy Set wb = ActiveWorkbook With wb .SaveAs Fpath _ & Format(.Worksheets(1).Range("c2"), "dd-mmm-yy") & ".xls" '.SendMail .Close False End With Application.ScreenUpdating = True End Sub I don't see anything that would change the date to a different cell. Hi Dave, I've done a little more checking - when Iselect the date from the combo box it's at this point that the date is copied to the cell C2 in the dates worksheet, not as I thought, when I hit the send button on the form. I wonder if there is something in the Combo Box properties - I've had a lokk and there is nothing obvious that would cause this. Still can't get the row to delete - it's still only the content of A1 that is cleared. -- Cheers Peter |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro problem - help please
Look at the linked cell property--or code behind that combobox.
And if you put a break point at the top of your code, then click the Snd button, you can step through the code and check what's happening at each step. Peter wrote: On Sat, 07 May 2005 06:55:04 -0500, Dave Peterson wrote: Option Explicit Private Sub CommandButton_Snd_Click() Dim wb As Workbook Dim Fpath As String Fpath = "Y:\Callout rotas\" Application.ScreenUpdating = False With Sheets("dates") .Rows(1).Delete end with worksheet("Rotas").Copy Set wb = ActiveWorkbook With wb .SaveAs Fpath _ & Format(.Worksheets(1).Range("c2"), "dd-mmm-yy") & ".xls" '.SendMail .Close False End With Application.ScreenUpdating = True End Sub I don't see anything that would change the date to a different cell. Hi Dave, I've done a little more checking - when Iselect the date from the combo box it's at this point that the date is copied to the cell C2 in the dates worksheet, not as I thought, when I hit the send button on the form. I wonder if there is something in the Combo Box properties - I've had a lokk and there is nothing obvious that would cause this. Still can't get the row to delete - it's still only the content of A1 that is cleared. -- Cheers Peter -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro problem help please. | New Users to Excel | |||
Macro Problem | Excel Discussion (Misc queries) | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
macro problem | Excel Worksheet Functions |