How to name a Sheet in an Excel Workbook? 2

[belstoy]

I am looking for a way to name a sheet in an Excel Workbook without having to identify the sheet as "Sheet 1" or "Sheet 2" etc...

This job will be on a loop that is naming multiple sheets using values within cells.

Is is possible to name a worksheet just by identifying it as "ActiveWorkSheet"??

Right now the only code I can find is this:

Sheets("sheet1").name = Range("a1").value

Thanks,
Belstoy

 

[SkipVought]

Hi,

"This job will be on a loop that is naming multiple sheets using values within cells"

dim ws as worksheet
for each ws in worksheets
  if ws.cells(1,1).value <> "" then _
    ws.name = ws.cells(1,1).value
next

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[AnotherHiggins]

So close!

Take the "Work" out of "ActiveWorkSheet"

that leaves you with ActiveSheet.Name = Range("a1").value

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[belstoy]

Thanks all,

This is very helpful!

 

[SkipVought]

BTW, althought John's answer is absolutely correct, be aware that it is neither necessary nor prudent to use the Select or Activate methods prior to changing an object's property of using an object's method.

In most cases, a Select or Activate ought to only be the last method used in order to position the display for the user as required.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[AnotherHiggins]

Indeed, while I did manage to answer your specific question, I should have gone a step further and mentioned that it is best to avoid using Select or Activate and provided a loop like the one Skip did.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[belstoy]

Thank you both for your guidance and advice.

Belstoy