How to use VBA to name sheet ?

[vumian]

I have a file that i do not know how many sheet are there in before

i need to copy contain of C4 to name of sheets

Thank for your help.

 

[Bob Phillips]

For Each sh In Activeworkbook.Worksheets

sh.Name = sh.Range("C4").Value

Next sh

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[vumian]

hi,

sorry, i fail to understand your mean

Could you be more specific,pls

Thank you.

 

[SteveW]

Create and run that macro.

Will do the job.

Unless you left something out of the original query.

Steve

 

[vumian]

thanks your idea, but here can not create macro coz i do not know how
many sheet before ?

How to use For statement here ?
VBA only.

thanks for your help.

 

[SteveW]

Why VBA only,

Anyway I count Excel macros as VBA

"For Each sh In Activeworkbook.Worksheets"

That does the counting for you.

 

[vumian]

Dim sh As Worksheets
Sub GetName()
For Each sh In ActiveWorkbook.Worksheets
sh.Name = sh.Range("C4").Value
Next sh
End Sub


It do not run.
Why, help me pls

 

[SteveW]

Works perfectly without the Dim statment - not needed

Steve

 

[vumian]

thank you very much, so great. man :

 

[Bob Phillips]

I would advise against suggesting that variables are not declared, poor
programming practice. The problem is that it should be declared as Worksheet
against Worksheets, not that it shouldn't be declared.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

Works perfectly without the Dim statment - not needed

Steve

 

[SteveW]

Quite right Bob,

Just the end of a long line of replies

 

[vumian]

hi everyone,

with code above, there is a error as following:

if value of C4 of every sheet same, it can not run code above.

how to msgbox in case

Sub GetName()
For Each sh In ActiveWorkbook.Worksheets
sh.Name =3D sh.Range("C4").Value
if .... What is it here to msgbox, pls
Next sh
End Sub