New Sheet Name

  • Thread starter Thread starter a
  • Start date Start date
A

a

Hi All,

is there anybody that could tell what code to use to insert a new worksheet
with a specific name. Currently I can create a new worksheet in a amacro
which will be named "Sheet1" and I can manipulate that sheet using the
macro. However, if I delete this sheet and then run the macro again, it will
be named "Sheet2" and all subsequent code is then referring to a
non-existent sheet e.g. "Sheet1".

This has been drivin me nuts for ages.
Thanks in advance,
Dominic
 
Thanks very much. I knew it had to be something very simple but just
couldn't work it out.

/Dom
 
One way:

Public Sub AddSheet()
Const sNAME As String = "Test"
Dim wsSheet As Worksheet
On Error Resume Next
Set wsSheet = Sheets(sNAME)
On Error GoTo 0
If wsSheet Is Nothing Then
Worksheets.Add.Name = sNAME
Else
MsgBox "sheet """ & sNAME & """ exists"
End If
End Sub
 
I don't know how to make Excel add the new sheet with the name you
specify, but you can easily change the name immediately after the
sheet is created.

Sheet.Add makes the new sheet the active one. Therefore, your code
can look like this:

Sheets.Add
Sheets(ActiveSheet.Name).Name = "Sheet1"

Alternatively, you could create a variable to store the name of the
spreadsheet once it's created. Then, every time you have
Sheets("Sheet1") in your code, you can replace the "Sheet1" with the
variable.
 
Just out of interest. I spent ages wading through the help files supplied
with Excel and could not find an answer to this. Perhaps I'm looking in the
wrong place or using help in the wrong way.

My question is, where would I be able to find information about this i.e. is
there some kind of reference document/book that describes all of the
available objects/methods/parameters (I'm probably using the wrong
terminology here) that are available in Excel and what they do?

Any help greatly appreciated.
/Dom
 
Back
Top