Macro problem - help please

[Peter]

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 "(e-mail address removed)", Format(Range("J4"), "dd-mmm-yy")
.SendMail "(e-mail address removed)", 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.

 

[Don Guillett]

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

 

[Peter]

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

I get an error message when I try this:

Run-time error '9'
Subscript out of range

 

[Dave Peterson]

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]

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 "(e-mail address removed)
Format(Range("c2"), "dd-mmm-yy")

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

 

[Dave Peterson]

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 "(e-mail address removed)
.Close False
End With
Application.ScreenUpdating = True

End Sub

 

[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 "(e-mail address removed)
.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 "(e-mail address removed)",Format(Range("c2"), "dd-mmm-yy")


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

End Sub

 

[Dave Peterson]

What worksheet should be copied?

If it's Dates, then this was ok.

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]

What worksheet should be copied?

If it's Dates, then this was ok.


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.

 

[Dave Peterson]

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 "(e-mail address removed)
.Close False
End With
Application.ScreenUpdating = True

End Sub

I don't see anything that would change the date to a different cell.

 

[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 "(e-mail address removed)
.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.

 

[Dave Peterson]

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.