I am setting up a daily cash settlement. I created a master sheet to be used
everyday. I am attempting to write a macro (Start New Period) that will ask
the user to input the first date of the period; then the macro should create
27 new worksheets (one for each day in the period) and proceed to rename them
sequentially by date starting with the date entered into the input box. The
part I am having trouble with is the line for renaming the sheet. I've tried
differing syntax but haven't gotten it right yet. How do I complete the line
with a variable name? activsheet.name:= DT step 1
where DT = the date entered into the input box.

Give this a whirl

Dim dte As Date
Dim lng As Long
Dim wks As Worksheet

dte = CDate(InputBox("Please enter a Date"))
For lng = 0 To 26
Set wks = Worksheets.Add
wks.Name = Format(dte + lng, "Mmm dd")
Next lng

--
HTH...

Jim Thomlinson


"KevinPinDC" wrote:

I am setting up a daily cash settlement. I created a master sheet to be used
everyday. I am attempting to write a macro (Start New Period) that will ask
the user to input the first date of the period; then the macro should create
27 new worksheets (one for each day in the period) and proceed to rename them
sequentially by date starting with the date entered into the input box. The
part I am having trouble with is the line for renaming the sheet. I've tried
differing syntax but haven't gotten it right yet. How do I complete the line
with a variable name? activsheet.name:= DT step 1
where DT = the date entered into the input box.

How about something like this:

Sub AddWS()
Dim myDate As Date
Dim myString As String
Dim myNewDate As Date

Do

myDate = InputBox("Enter first date as MM-DD-YYYY")
Debug.Print myDate

Loop While myDate = 0


For i = 0 To 26
Set myWS =
ThisWorkbook.Sheets.Add(After:=Worksheets(Workshee ts.Count))
myNewDate = myDate + i
Debug.Print myNewDate
myString = Year(myNewDate) & "-" & Format(Month(myNewDate), "00") &
"-" & Format(Day(myNewDate), "00")
myWS.Name = myString
Next i


End Sub

HTH,
Barb Reinhardt

"KevinPinDC" wrote:

I am setting up a daily cash settlement. I created a master sheet to be used
everyday. I am attempting to write a macro (Start New Period) that will ask
the user to input the first date of the period; then the macro should create
27 new worksheets (one for each day in the period) and proceed to rename them
sequentially by date starting with the date entered into the input box. The
part I am having trouble with is the line for renaming the sheet. I've tried
differing syntax but haven't gotten it right yet. How do I complete the line
with a variable name? activsheet.name:= DT step 1
where DT = the date entered into the input box.

One more...

Option Explicit
Sub testme()

Dim myDate As Long
Dim iCtr As Long

myDate = Application.InputBox(prompt:="Enter a date", _
Default:=Format(Date, "mmmm dd, yyyy"), Type:=1)

'minor testing
If Year(myDate) < 2007 _
And Year(myDate) 2020 Then
MsgBox "Only between 2007 and 2020"
Exit Sub
End If

For iCtr = myDate + 26 To myDate Step -1
Worksheets.Add.Name = Format(iCtr, "yyyy-mm-dd")
Next iCtr

End Sub

I like to use application.inputbox() with type:=1. It only allows the user to
enter a number (and dates are numbers).

KevinPinDC wrote:

I am setting up a daily cash settlement. I created a master sheet to be used
everyday. I am attempting to write a macro (Start New Period) that will ask
the user to input the first date of the period; then the macro should create
27 new worksheets (one for each day in the period) and proceed to rename them
sequentially by date starting with the date entered into the input box. The
part I am having trouble with is the line for renaming the sheet. I've tried
differing syntax but haven't gotten it right yet. How do I complete the line
with a variable name? activsheet.name:= DT step 1
where DT = the date entered into the input box.

--

Dave Peterson