P
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.
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.