Solved: Naming Sheets

[zoom38]

Hello everyone, I'm trying to name 28 consecutive sheets beginning with the date that is in on the "RVSD Squad 1" worksheet in cell S1. The following sub works fine for the 1st sheet but I can't seem to get it to work when I go to the next sheet. I'm naming the sheets with the month and day "Jan 27". The next sheet needs to be named "Jan 28" and so on for 28 days. Adding 1 to Day doesn't work. Can someone steer me in the right direction.
Thanks
Gary


[VBA]
Sub NamePers()

Dim Sheet As Integer
Dim Day

MyDate = Worksheets("RVSD Squad 1").Cells(1, 19).Value
Day = Format(MyDate, "mmm d")


For Sheet = 8 To 9
Sheets(Sheet).Activate

With ActiveSheet
.Name = Day
End With

MyDate = MyDate + 1
Next Sheet

End Sub
[/VBA]

[Bob Phillips]

[vba]

Sub NamePers()

Dim Sheet As Long

MyDate = Worksheets("RVSD Squad 1").Cells(1, 19).Value

For Sheet = 8 To 9
Sheets(Sheet).Name = Format(MyDate, "mmm d")
MyDate = MyDate + 1
Next Sheet

End Sub
[/vba]

[Bob Phillips]

BTW, the problem you were experiencing was because you weren't adding 1 to the Day variable as you said, and nor you should as it is a string, but you were not setting Day to the format of the incremented date variable, MyDate, to the date format as you did at the start.

As it happens, I removed much of the swapping of the data in the variables.

[zoom38]

Thanks XLD but I still get an error. I'm getting a "Type Mismatch" error on the MyDate = MyDate +1 line.
Any thougts?

[mdmackillop]

Not sure why your getting an error, but MyDate should be dimmed as well
[VBA] Dim Sheet As Long
Dim MyDate As Date
[/VBA]

[Norie]

Don't use Day as a variable name Day is a VBA function.

What's the exact code you are getting the error with?

Like mdmackillop I can't see why you are getting the error.

[zoom38]

Thanks XLD, my stupidity. After I dimmed MyDate it works fine.
Norie I'm using the exact code that XLD gave.

By the way XLD you Dimmed Sheets as long, I will only have a maximum of 34 sheets, should it be Long?

Thanks Again
Gary

[mdmackillop]

Hi Gary
Variables dimmed as Integer get changed to Long by the system anyway, so there's no memory or other advantage.

[zoom38]

Thanks for the explanation Md.

I have one more question. Ive deleted and added worksheets a number of times and now the sheets are not numbered sequentially anymore in the vba project window. Is there a way to manually change the sheet numbers?

Thanks

[Bob Phillips]

[vba]
Dim i As Long
Dim sh As Worsheet

For Each sh In Activeworkbook.Worksheets
sh.Name = "_Sheet" & i
Next sh

For Each sh In Activeworkbook.Worksheets
sh.Name = "Sheet" & i
Next sh
[/vba]

[zoom38]

Im not sure what you have here XLD, I already was able to name the sheets with your help. Now I just want to consecutively number the sheets as if I never deleted sheets. When I say number the sheets I don't mean the name.
In the VBA project window the sheets are listed as follows:

Sheet1 (RVSD Squad 1)
Sheet2 (RVSD Squad 2)
Sheet3 (Week1)
Sheet4 (Week2)
Sheet41 (Sheet32)
Sheet5 (Week3)
Sheet6 (Week4)
Sheet7 (Tables)
Sheet8 (Jan 11)
Sheet9 (Jan 12)


Now when I add another sheet it will be be labled Sheet42. I will have the sub you gave me name it with a date. I would like to be able to manually change the Sheet lables that are assigned by excel to read "Sheet1" through "Sheet35" regardless of the sheet names.

Thanks
Gary

[Bob Phillips]

You mean the codenames then?

Why? I have only ever changed codenames to get a meaningful name in there, never to just have an ordered list. Seems pointless to me.

[mdmackillop]

I agree with XLD. If still required though, you can change the codenames in the Properties window. Check out CodeNames Property in VBA Help

[Bob Phillips]

I think he wants to have a macro to do it so that he can run after every sheet add/delete.

[mdmackillop]

I guessed that too Bob, but according to this, it doesn't seem possible.

CodeName Property

Returns the code name for the object. Read-only String.
Note The value that you see in the cell to the right of (Name) in the Properties window is the code name of the selected object. At design time, you can change the code name of an object by changing this value. You cannot programmatically change this property at run time.

[zoom38]

Thanks, no I really don't need a macro to do it. I just wanted to put them in consecutive number order expecially sheets 8 - 35 so my sub won't run into problems finding the sheets. I havn't tested it to see if it would run into problems but I can't see how it wouldn't if the numbers were off.
Thanks Again
Gary

[vba]
Sub NamePers()

Dim Sheet AsLong
Dim MyDate As Date

MyDate = Worksheets("RVSD Squad 1").Cells(1, 19).Value

For Sheet = 8 To 35
Sheets(Sheet).Name = Format(MyDate, "mmm d")
MyDate = MyDate + 1
Next Sheet

End Sub[/vba]

[Bob Phillips]

But the codenames are irrelevant to the worksheet names. I suggest you don't worry about it until you actually get a problem.