Sheets.name

[SteveDB1]

Hi all.
I have a worksheet creation macro that I made by recording. Everything works
great-- until.... (ain't that always the way it goes.......)
I had it working fine until I got to one workbook in particular that the
names extended beyond how many worksheets were actually in the book (this
happens occasionally here).
I'd originally wanted to name the worksheet manually each time I made a new
one, but since I recorded this macro, and was unfamiliar at the time with
input boxes, I used the following:

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheets.Count

What I'd like to do is to have an input box that I can give the new
worksheet a name of my choosing.

I tried a variation on the above and found that it's not worked.


Dim NewNm As String
NewNm = InputBox(Prompt:="What is the sheet number you want to call this _
worksheet?", Title:="New Abstract Worksheet Name")

Sheets.Add After:=Sheets(Sheets.Count)
Sheets.Name = NewNm

what did I miss here?

It seems to get hung up at the sheets.name.
Thank you, in advance for your responses.

 

[cht13er]

Hi all.
I have a worksheet creation macro that I made by recording. Everything works
great-- until.... (ain't that always the way it goes.......)
I had it working fine until I got to one workbook in particular that the
names extended beyond how many worksheets were actually in the book (this
happens occasionally here).
I'd originally wanted to name the worksheet manually each time I made a new
one, but since I recorded this macro, and was unfamiliar at the time with
input boxes, I used the following:

Sheets.Add After:=Sheets(Sheets.Count)
  Sheets(Sheets.Count).Name = Sheets.Count

What I'd like to do is to have an input box that I can give the new
worksheet a name of my choosing.

I tried a variation on the above and found that it's not worked.

Dim NewNm As String
NewNm = InputBox(Prompt:="What is the sheet number you want to call this _
worksheet?", Title:="New Abstract Worksheet Name")

   Sheets.Add After:=Sheets(Sheets.Count)
  Sheets.Name = NewNm

what did I miss here?

It seems to get hung up at the sheets.name.
Thank you, in advance for your responses.

See how above it said "Sheets(Sheets.Count).Name = Sheets.Count" and
lastly you tried to do "Sheets.Name = NewNm"?

It doesn't know which sheet to name "NewNm" - so change that last line
to Sheets(Sheets.Count).Name = NewNm and you should be OK

HTH

Chris

 

[SteveDB1]

that was it....
Thanks.

See how above it said "Sheets(Sheets.Count).Name = Sheets.Count" and
lastly you tried to do "Sheets.Name = NewNm"?

It doesn't know which sheet to name "NewNm" - so change that last line
to Sheets(Sheets.Count).Name = NewNm and you should be OK

HTH

Chris

 

[Dave Peterson]

Or...

Sheets.Add After:=Sheets(Sheets.Count)
activesheet.name = NewNm