Solved: Adding new sheets with the pre-define names

[Kaizer]

Hi guys. I need your help with the VBA code. I want to control adding the sheets into the WorkBook. The code must add new sheets with the pre-defined names.

When I add it manually Excel gives new name and new description (e.g. Sheet11 (Sheet6)). I would like to make sure that it's called something like Sheet11 (My Sheet1). The next one can be Sheet12 (My Sheet2).

Thank you.

[lucas]

Hi Kaizer,
Here's a great reference for understanding sheet references:
http://vbaexpress.com/forum/showthread.php?t=9771

If your trying to change the code name of a sheet in the vbe....I don't think you can do that except manually......someone tell me if I'm wrong.

[Bob Phillips]

I could understand if you wanted to give meaningful names, but why would you care if it just gives the generic Sheet name?

The minor inconvenienc in the code is that it would have to check if the name already exists and increment if so, bit wasteful for generic names IMO.

[Bob Phillips]

Hi Kaizer,
Here's a great reference for understanding sheet references:
http://vbaexpress.com/forum/showthread.php?t=9771

If your trying to change the code name of a sheet in the vbe....I don't think you can do that except manually......someone tell me if I'm wrong.

You are wrong.



With ActiveSheet
.Parent.VBProject.VBComponents(.CodeName) _
.Properties("_CodeName") = "somevalue"
End With

[lucas]

Indeed I am.....

[Kaizer]

I could understand if you wanted to give meaningful names, but why would you care if it just gives the generic Sheet name?

The minor inconvenienc in the code is that it would have to check if the name already exists and increment if so, bit wasteful for generic names IMO.

I actually want to give the sheets some meaningful names (e.g. DT_Rev_Tx). In my original post I thought I put the easiest example for making code. I thought I would look at how it is created and apply my own names.

[lucas]

Maybe something like this Kaiser. It uses a constant string such as 2006 in the example but it can be changed to MySheet or any other string. It then adds a number. Each sheet is added as the last sheet in the workbook:
Sub AddNewSheets()
Dim TotalSheets
TotalSheets = Worksheets.Count - 1
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "2006 " & TotalSheets + 1
ActiveSheet.Visible = True

End Sub

[Kaizer]

Thank you, lucas. I think this should work for me.