Excel Worksheet data range

Microsoft Office 2007 basic edition with...
August 6, 2012 at 09:02:39
Specs: Windows XP
Okay so here is what i am trying to do I have a worksheet called template and i was modifying the below VBA to copy that Worksheet and then change the name to a date range. Now if i remove template from Set wsNew = ActiveWorkbook.Worksheets("template").Add(After:=Worksheets(Worksheets.Count)) it will work making new worksheets with the date range but with that template line it copies template but i need to know how to get it to rename that to the date range i need. Any help would be awesome

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

'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("template").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

End Sub

See More: Excel Worksheet data range

Report •

August 8, 2012 at 07:08:37
First, a posting tip...

Please click on the blue line at the end of this post and read the instructions on how to post data and/or VBA code in this forum.

It looks like your code adds a new worksheet as the last worksheet in the workbook. Therefore, this code should rename the last worksheet by referring to it by its "sheet number" since Sheets.Count will always reflect the highest sheet number in a workbook.

Sheets(Sheets.Count).Name = (Whatever you want your sheet name to be)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 9, 2012 at 11:14:43
Thanks but what i am concerned about is i can get it to create the date range as a worksheet
so if i put 08/10/12 - 08/12/12

You will see it creates workssheet 08.10.12, 08.11.12 and 08.12.12 but they are blank. What i need it to do is copy "template" and rename it to the same date range but when i try to do that it just copies template (1) and stops.

Report •

Related Solutions

Ask Question