SOLVED: How do you create a worksheet with a given name?

[jambarama]

I've been a lurker here for quite some time, this is a great forum. Well a few months ago I finally signed up, and now I am making my first post.

My question is simple, how do you create a worksheet with a specified name?

I am writing a macro for excel 2003. It will be run on a variety of workbooks with a variety of worksheet names. I can add a worksheet
Dim tempwb As Worksheet
Set tempwb = Application.Worksheets.Add and I can rename an existing worksheet, but I can't figure out how to do any of the following:
1. List the worksheet names in a workbook - putting them in an array would be ideal
2. Find the name of the most recently added worksheet - storing this as a string would be lovely
3. Add a worksheet with a specified name

This causes problems because this macro will be run on some workbooks that already have a "Sheet1" worksheet, so the new worksheet the macro creates will be Sheet2, or Sheet3 or whatever.

I've searched the net, as well as Programming Excel with VBA & .NET and Writing Excel Macros with VBA both by O'Reilly publishing.

Anyone know?

[Norie]

1 Why do you want to do this?

2 Why do you need the name? You already have a reference to the worksheet, tempwb.

3 To set a worksheet name:

wbtemp.Name="WorksheetName"

[lucas]

This will add a new sheet to the end and name it:
2006 3
the 2006 part is a string which you can change...the number is the sheet count + 1 each time so it changes when you run it and incriments each time to the next number.


Option Explicit
Sub AddNewSheets()
Dim TotalSheets
TotalSheets = Worksheets.Count - 1
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "2006 " & TotalSheets + 1
ActiveSheet.Visible = True

End Sub

[lucas]

You can also use a cell value to name the sheet. Look for the ActiveSheet.Name line in the code of post #3 you can replace that line with the following to use a cell value as the new sheet name:

ActiveSheet.Name = Range("G3").Value

[CBrine]

For question number 1 above


Sub GetAllSheets()
dim wb as workbook, ws as worksheet
dim sheetArray() as string, count as integer
set wb = activeworkbook

Count=0

ReDim SheetArray(wb.Worksheets.Count-1)

for each ws in wb.worksheets
SheetArray(Count)=ws.name
Count=Count+1
Next ws

End Sub


Question 2
There is no way to determine what the most recently added worksheet is that I'm aware of.

Question 3
I think Lucas and Norie answered this one in thier posts.

[jambarama]

You guys are awesome, thanks so much. Questions 1 & 2 were just workarounds, if question 3 was non-trivial. Thanks again.

[CodeMakr]

Question 2: couldn't you add a date stamp to a reference cell to the "workbook add" and keep track of which came first that way?

[Gert Jan]

Question 2: couldn't you add a date stamp to a reference cell to the "workbook add" and keep track of which came first that way?
Maybe change Steve's code a little bit,
Sub AddNewSheets()
Dim TotalSheets
TotalSheets = Worksheets.Count - 1
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = TotalSheets + 1 & " " & Date
ActiveSheet.Visible = True

End Sub

Gert Jan

[Zack Barresse]

Interesting thread. If you were to "time stamp" each sheet, you may be better off having a "control" sheet and a workbook event to catch when you add/delete sheets and keep the information separate.

[SamT]

An interesting thing in VBA Excel 97.

Except in the case where I opened a new sheetless workbook and started by inserting several sheets, VBA created the "Codenames" (Sheet1, Sheet2. etc.) in the order the worksheet was added regardless of where the new sheet was Inserted.

In the exceptional case, the codenames are in tab order, but the tab names are in creation order.

CN(Sheet1) = TN(Sheet5)
CN(Sheet2) = TN(Sheet4)
.
.
.
CN(Sheet5) = TN(Sheet1)

Where Tab Name Sheet1 was the first created and all others were Inserted before the previously created.

I did not look at the VBA IDE until after I had inserted 5 sheets. This may change the results. (y'all know how consistant things are)

Maybe post this gives someone some ideas???

SamT

ps: I'm ass*u*me-ing that Sheet(index) is always in Tab Order
pps: This would be just a little less confusing if MS had used a different style for Tab Names, like "Sheet_1".

[Zack Barresse]

Sam,

In the Project Explorer, they are only listed in [sheet code name] alphabetical order, not necessarily the order they appear in the workbook(s).