How to combine these macros into one

[mikeyboy]

Every two weeks I need to create a copy of "Blank 2K" sheet using the "Blank2KmovetoEnd" macro run from a button on the last sheet in the workbook and it's becomes the new last sheet called "Blank2K (2)".

Sub Blank2KmovetoEnd()
'
Sheets("Blank 2K").Select
Sheets("Blank 2K").Copy After:=Sheets(Sheets.Count)
Sheets("Blank 2K (2)").Select
Range("B9").Select
End Sub

Then I manually enter the date plus 1 day from the previous sheet cell "E2" (the sheet I used the button to run the "Blank2KmovetoEnd" macro)into cell "B2". I then run the "RenameSheet" macro which renames the "Blank2K (2)" sheet to use dates from cells "B2" and "E2"


Sub RenameSheet()
ActiveSheet.Name = Format(Range("B2"), "dd-mmm-yy") & _
" - " & Format(Range("E2"), "dd-mmm-yy")

End Sub


What I'd like to do is make all this happen at the click of one macro button. It might be important to know that cell "E2" formula is =B2+13 to automatically give me the 2 weeks dates the sheet is applicable to.

Any ideas would be appreciated.

Thanks

[topswim]

See if this helps. I created some macros to create a sheet and rename it for my tennis team. What happens here is that a copy of "Demplate" is made, like you have done in your example. I than go to my "info" sheet, which you can go back to your original one, and select the cell I want to use for the sheet name. When I recorded the macro it was a matter of copying the text in "d3" and pasting it in the new sheet tab.

Sheets("DTemplate").Select
Sheets("DTemplate").Copy Before:=Sheets(3)
Sheets("DTemplate (2)").Select
Sheets("info").Select
Sheets("DTemplate (2)").Name = Range("d3").Text

Hope this helps.


[ This Message was edited by: topswim on 2002-05-18 06:18 ]

[Tom Schreiner]

I have a different understanding of what you are trying to do...
Hopefully, one of us has it right!

Private Sub CommandButton1_Click()

Dim DatePeriod As Date

DatePeriod = Range("E2")

With Sheets("Blank 2K")

    .Copy After:=Sheets(Sheets.Count)

    ActiveSheet.Name = Format(DatePeriod + 1, "dd-mmm-yy") & _

    " - " & Format(DatePeriod + 15, "dd-mmm-yy")

    ActiveSheet.Range("B2") = DatePeriod + 1

    ActiveSheet.Range("E2") = DatePeriod + 15

End With

End Sub

Tom


[ This Message was edited by: TsTom on 2002-05-18 07:30 ]

[mikeyboy]

Topspin and Tom, Toms version worked perfectly with the exception of 15 days needed to be 14. Pretty awesome!

Thank you both very much for the assistance!!