Rename existing tabs

[Guest]

I have a monthly worksheet that requires one worksheet tab for each day.
Every month, I take my master sheet and manually rename each tab for the next
month.

Is there any way this could be done with a macro? If so, can someone
provide sample code? I have some experience with recording macros for Word,
but I'm definitely no programmer, so I can't actually write one myself!
Thanks for any help anyone can provide!

 

[Tom Ogilvy]

for each sh in Thisworkbook.Worksheets
sh.name = sh.Name & "aa"
Next

 

[Guest]

What I understand is that you have a workbook which has a worksheet called
Master and you want to copy that Master worksheet 30/31 times and rename each
with a Date. For instance the first tab as 1, the second tab as 2 and so on.
If this is what you require then the following will do it

Sub Test()
Dim i%
For i = 31 To 1 Step -1
Worksheets("Master").Copy After:=Worksheets(1)
ActiveSheet.Name = i
Next i
End Sub

After it does the creation of the sheets, manually delete the tabs not
required.

Alok Joshi

 

[Guest]

Yes

Oh that's beautiful, that works like a charm, thank you! When I have to
change the year, do I just change the number "2005" in both places in the
code to the current year?

Thanks again for your help!

 

[Guest]

This should do it

Sub Test2()
Dim i%
Dim m As Variant
m = InputBox("Enter Month Number Jan=1, Feb=2..", "Month")
If m = "" Then Exit Sub
For i = Day(DateSerial(2005, Val(m) + 1, 0)) To 1 Step -1
Worksheets("Master").Copy After:=Worksheets(1)
ActiveSheet.Name = Format(DateSerial(2005, Val(m), i), "mm-dd-yy")
Next i
End Sub

Alok Joshi

 

[Guest]

Thanks Alok! This is pretty close to what I need, and is actually better
than what I was thinking in that it creates all 31 sheets each time, which
will make it easier if I have to update the format of the sheet. Cool!

The only thing I would like differently is for the tab names not to be just
numbered 1-31. I would like them to be the actual dates. 5-1-05, 5-2-05,
etc. If this is too complicated, I do think just using the numbers 1-31
will be acceptable. But if you have an idea on how to get the actual date
onto each tab, that would be great.

Thanks for your help!

 

[Guest]

Oh that's beautiful, that works like a charm, thank you! When I have to
change the year, do I just change the number "2005" in both places in the
code to the current year?

Thanks again for your help!

 

[Guest]

Thinking about this, I can actually combine these two macros do what I am
trying to do! I can use Alok's macro to generate 31 uniquely numbered tabs,
then I can use Tom's macro to append the appropriate month's number to the
beginning of each tab.

If anyone can think of a way to have Tom's macro ask me which month and year
I want to add to the tab, that would be great. But this combination of the
two works wonderfully -- even if I need to edit the macro each time I run it
that's a vast improvement over my current manual system! Thanks so much for
your help, guys!

Sub Macro2()

Dim i%
For i = 31 To 1 Step -1
Worksheets("Master").Copy After:=Worksheets(1)
ActiveSheet.Name = i
Next i

For Each sh In ThisWorkbook.Worksheets
sh.Name = "07-" & sh.Name & "-05"
Next


End Sub

 

[Guest]

Hi -- thanks, I tried this but what it seems to do is add "aa" to the
existing name of each tab? (Do I understand correctly what this macro is
doing?) This doesn't quite work for my purposes since it adds the exact same
thing to each tab.

Each of my tabs is named with a specific date. So when I create my workbook
for June, the tab called 5-1-05 needs to be renamed 6-1-05, 5-2-05 needs to
be renamed 6-2-05, etc. So I guess I really need the macro to replace the
first number, whatever it is. Ideally, I guess it would ask me what number I
want to use. I suppose it would also work if it just looked at the current
number and added one.

Thanks again for for any help you can offer, I appreciate it!