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

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


--
Don Guillett
SalesAid Software

"Peter" wrote in message
...
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

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

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

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

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