Sheet Numbers

[albertmb]

Hi Everyone,

Is it possible to have a macro changing sheet numbers automaticaly. My
workbook contains over 300 sheets starting from number 1.

Thank you

Albert

 

[Gord Dibben]

You just want them numbered 1 to 300?

Enter the numbers 1 to 300 in column A of first sheet.

Run this macro.

Sub NameWS()
'name sheets with list in A1:A300 on first sheet
On Error Resume Next
For i = 1 To Worksheets.Count
Sheets(i).Name = Sheets(1).Cells(i, 1).Value
Next i
End Sub


Gord Dibben MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

Do you really need to put the numbers in Column A? This would work, right?

Sub NameWS()
Dim i As Long
On Error Resume Next
For i = 1 To Worksheets.Count
Sheets(i).Name = i
Next i
End Sub

Rick

 

[albertmb]

Thank You all, you are marvelous people.
Rick, it worked perfectly.

 

[Gord Dibben]

True, you don't need the numbers in Column A.

Your code works fine.

I posted standard code that will use whatever is in column A, be it numbers or a
list of unique names of all my grand-children.

But I wouldn't need A1:A300<g>.


Gord

 

[Rick Rothstein \(MVP - VB\)]

I posted standard code that will use whatever is in column A, be it

numbers
or a list of unique names of all my grand-children.

But I wouldn't need A1:A300<g>.

LOL

Rick

 

[albertmb]

Rick I kept a copy of your macro to, but I still did not have the time to try
it out, I will let you know.
Thank you ever so much
Albert

 

[Rick Rothstein \(MVP - VB\)]

I'm confused. In this message (the one I'm responding to) you are saying you
didn't try my macro out, but earlier today (about 7.5 hours ago) you said
this..

"Thank You all, you are marvelous people.
Rick, it worked perfectly."

If you didn't try my macro out, what was it that "worked perfectly" earlier
in the day?

Rick

 

[albertmb]

I apologise Rick, it was my mistake, I did use your macro, that message was
ment for Gordon. But I still think you are all marvelous people, before I
got to know about this discussion Group I thought I knew how to work with
Excel but all of you amaze me, you have an answer to everything. Well-done
and thank you all for the time you dedicate to people like me.

Regards

Albert

 

[Rick Rothstein \(MVP - VB\)]

No apology necessary... I just found the contradiction confusing, that is
all.

I've only been volunteering in the Excel newsgroups for about 9 months, and
my overall knowledge of Excel is still limited, so I don't think I belong in
the group you are singling out at this point in time (one day, I hope, but
not yet). However, I do concur with you and would like to join you in
applauding the efforts of the long-time regulars here... they ARE truly a
marvelous group of people.

Rick