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