Copying sheets with a date and a sequential number

[TheScott]

In my quest to write something that would
1.copy a template to a new sheet
2.Rename the sheet to the current month and day (MMM-DD)
3 Add to the date a sequencial number if another sheet were created on the same day

Please Login or Register  to view this content.

The code appears to work in isolation but but fails in two respects

1. If only the template sheet exists in the workbook sequencial numbering begins with '1'.'MMM-DD-1'
but if other sheets exists the numbering starts with the number of other sheets plus 1.
I.E. if 'Template' and another sheet 'X' exists, the macro will copy the template and name it
'MMM-DD-2 rather than 1. That sequence will continue with 2..3..etc.

2. The other problem is that when the day changes the number sequence continues from the previous day rather
than resetting with the new date as I would like.

I understand the problem is that the macro is focused on the sheet number rather than the number of copies of the template on a given date, I just don't know how to address the issue.

Thanks in advance

Scott

[watersev]

hi there, please check attachment, press Add button

[TheScott]

Thank you very much. I never would have gotten there. InStr is a new function to me.
I looked it up and am still not quite clear on its usage but it is one to understand forsure.
I am also confused about the usage of 'Mid' in the line:

sName = sName & "-" & Mid(sh.Name, InStrRev(sh.Name, "-") + 1) + 1

can you explain this to me?

Sorry for not putting an execution button on the spreadsheet... bad form

Again, Thank you for your help

Scott

[watersev]

InstrRev - returns the position of an occurrence of one string within another, from the end of string.
Syntax
InstrRev(stringcheck, stringmatch[, start[, compare]])

Mid function is used to return "occurence number" and increase it one up