Auto creating new sheet name 1

[eti3nne]

I have a spreadsheet with sheets named wc14jul08 then wc11aug08, etc These are a 4-weekly timesheet series with carry-over between sheets.

I would like to generate a new sheet automatically with the new period name as in this case 11aug08 + 28 days ie wc08sep08.

I have some incorrect code to construct the sheetname eg

CurrentShtName = ActiveSheet.Name
DateNew = (Mid(CurrentShtName, 3, 7)) + 28
NewSheet.Name = "wc" & DateNew
Set NewSheet = Worksheets.Add

I need excel to see the actual current sheet name ie wc11aug08 and some improved code.

Any ideas?

Thanks for looking.

 

[chandlm]

How about something like this ?

Dim mday, mMonth, myear As String
mday = Mid(ActiveSheet.Name, 3, 2)
mMonth = Mid(ActiveSheet.Name, 5, 3)
myear = Mid(ActiveSheet.Name, 8, 2)

Dim mDate As Date
mDate = DateValue(mday & " " & mMonth & " " & myear)

Dim newsheetname As String
newsheetname = "wc" & Format(mDate + 28, "ddMMMyy")
ActiveWorkbook.Worksheets.Add After:=ActiveSheet

ActiveSheet.Name = "wc" & Format(mDate + 28, "ddMMMyy")

Hope this helps

Matt

 

[PHV]

What about this ?

CurrentShtName = ActiveSheet.Name
DateNew = 28 + CDate(Mid(CurrentShtName, 3, 2) & "-" & Mid(CurrentShtName, 5, 3) & "-" & Right(CurrentShtName, 2))
Set NewSheet = Worksheets.Add
NewSheet.Name = "wc" & Format(DateNew, "ddmmmyy")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[eti3nne]

Thanks both chandlm and PHV. Both do the job. The extra code for locating the sheet to the right side of the current one was an added bonus!

Thanks again both.

Steve.

 

[chandlm]

Glad to help.

Thanks for the Star

Matt