Pam in TX
09-27-2017, 11:45 AM
Am trying to do the following based on the answers to the beginning and ending date questions
Create copies of the original sheet named "Master"
Rename the tabs the correct dates and eliminate the weekends
And if possible, put the date, ie name of tab, in cell K2
I can create the tabs and name the dates, but didn't copy the information from the original tab. Here is what I have so far. I appreciate your assistance.
Sub AddDatedWS()
Dim strStartDt As String
Dim strEndDt As String
Dim dtStart As Date
Dim dtEnd As Date
Dim wsNew As Worksheet
Dim n As Double
' Turns the screen off while the Macro is running.
Application.ScreenUpdating = False
'get start date
strStartDt = InputBox("Enter start date", "Create dated worksheets")
If Not IsDate(strStartDt) Then Exit Sub
'get end date
strEndDt = InputBox("Enter end date", "Create dated worksheets")
If Not IsDate(strStartDt) Then Exit Sub
'convert text to Excel's date format
dtStart = CDate(strStartDt)
dtEnd = CDate(strEndDt)
'test if start date equal to or later than end date
If dtStart >= dtEnd Then Exit Sub
'confirm number of sheets
If MsgBox("Create " & dtEnd - dtStart + 1 & " worksheets", vbOKCancel) = _
vbCancel Then Exit Sub
For n = dtStart To dtEnd
'create a new worksheet
Set wsNew = ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
'name it with a date (date text can't contain : \ / ? * [ or ])
wsNew.Name = Format(n, "mm.dd.yy")
Next n
'Turns the screen back on when the Macro is finished.
Application.ScreenUpdating = True
End Sub
Create copies of the original sheet named "Master"
Rename the tabs the correct dates and eliminate the weekends
And if possible, put the date, ie name of tab, in cell K2
I can create the tabs and name the dates, but didn't copy the information from the original tab. Here is what I have so far. I appreciate your assistance.
Sub AddDatedWS()
Dim strStartDt As String
Dim strEndDt As String
Dim dtStart As Date
Dim dtEnd As Date
Dim wsNew As Worksheet
Dim n As Double
' Turns the screen off while the Macro is running.
Application.ScreenUpdating = False
'get start date
strStartDt = InputBox("Enter start date", "Create dated worksheets")
If Not IsDate(strStartDt) Then Exit Sub
'get end date
strEndDt = InputBox("Enter end date", "Create dated worksheets")
If Not IsDate(strStartDt) Then Exit Sub
'convert text to Excel's date format
dtStart = CDate(strStartDt)
dtEnd = CDate(strEndDt)
'test if start date equal to or later than end date
If dtStart >= dtEnd Then Exit Sub
'confirm number of sheets
If MsgBox("Create " & dtEnd - dtStart + 1 & " worksheets", vbOKCancel) = _
vbCancel Then Exit Sub
For n = dtStart To dtEnd
'create a new worksheet
Set wsNew = ActiveWorkbook.Worksheets.Add(After:=Worksheets(Worksheets.Count))
'name it with a date (date text can't contain : \ / ? * [ or ])
wsNew.Name = Format(n, "mm.dd.yy")
Next n
'Turns the screen back on when the Macro is finished.
Application.ScreenUpdating = True
End Sub