Sheets.name

S

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.
 
C

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
 
S

SteveDB1

that was it....
Thanks.


cht13er said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top