Thread: 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 "xxxxx@xxxxx.com", Format(Range("J4"), "dd-mmm-yy")
.SendMail "xxxxx@xxxxx.com", 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

try
sheets("dates").rows(1).delete


--
Don Guillett
SalesAid Software
donaldb@281.com
"Peter" <usenetINVALID@nidum.plus.com> wrote in message
news:t4ok71h19f53ovjbr6lcftutasv0s7idt0@4ax.com...
> 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 "xxxxx@xxxxx.com", Format(Range("J4"), "dd-mmm-yy")
> .SendMail "xxxxx@xxxxx.com", 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

On Thu, 5 May 2005 13:27:19 -0500, "Don Guillett" <donaldb@281.com>
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

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" <donaldb@281.com>
> 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

On Thu, 05 May 2005 17:51:15 -0500, Dave Peterson
<ec35720@netscapeXSPAM.com> 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 "xxxx@xxxx.com
Format(Range("c2"), "dd-mmm-yy")

.Close False
End With
Application.ScreenUpdating = True
Unload Me


--
Cheers

Peter

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 "xxxx@xxxx.com
.Close False
End With
Application.ScreenUpdating = True

End Sub




Peter wrote:
>
> On Thu, 05 May 2005 17:51:15 -0500, Dave Peterson
> <ec35720@netscapeXSPAM.com> 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 "xxxx@xxxx.com
> Format(Range("c2"), "dd-mmm-yy")
>
> .Close False
> End With
> Application.ScreenUpdating = True
> Unload Me
>
> --
> Cheers
>
> Peter

--

Dave Peterson

On Fri, 06 May 2005 08:04:49 -0500, Dave Peterson
<ec35720@netscapeXSPAM.com> 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 "xxxx@xxxx.com
> .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 "xxx@xxx.com",Format(Range("c2"), "dd-mmm-yy")


.Close False
End With
Application.ScreenUpdating = True
Unload Me
End With

End Sub


--
Cheers

Peter

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
> <ec35720@netscapeXSPAM.com> 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 "xxxx@xxxx.com
> > .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 "xxx@xxx.com",Format(Range("c2"), "dd-mmm-yy")
>
>
> .Close False
> End With
> Application.ScreenUpdating = True
> Unload Me
> End With
>
> End Sub
>
> --
> Cheers
>
> Peter

--

Dave Peterson

On Fri, 06 May 2005 19:10:14 -0500, Dave Peterson
<ec35720@netscapeXSPAM.com> 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

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 "xxxx@xxxx.com
.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
> <ec35720@netscapeXSPAM.com> 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

On Sat, 07 May 2005 06:55:04 -0500, Dave Peterson
<ec35720@netscapeXSPAM.com> 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 "xxxx@xxxx.com
> .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

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
> <ec35720@netscapeXSPAM.com> 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 "xxxx@xxxx.com
> > .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