Rename Sheet name as per cell value

[santoshsapre]

Hi,


i have workbook having around 25 sheets , i want to change all sheet name as per value in cell A6.


Required macro for the same.

 

[SirJB7]

Hi, santoshsapre!


Tried recording one by yourself using the built-in macro recorder and analyzing the resulting code? You'll only need to add a For...Next loop, a string variable, and not much more.

Go to the Programmer tab, Code (first) group, Record Macro button. Or just press the dot at the left bottom corner of your screen.

You may find this process very useful and interesting if you plan to use macros everywhere as I realize for your started topics.


Regards!

 

[santoshsapre]

Sir


have tried to record macro as below.but not clear on the "Programmer tab, Code (first) group, Record Macro button. Or just press the dot at the left bottom corner of your screen." can u pls help.


Sub Macro3()

'

' Macro3 Macro

'


'

Range("A6").Select

Selection.Copy

Sheets("sheet1").Select

Sheets("sheet1").Name = "wnwd"

Range("A6").Select

Application.CutCopyMode = False

End Sub

 

[Luke M]

As SirJB7 suggested, you should really try it first on your own. You could even then try to optimize it to make it run faster. But, for the sake of answering the question, here's the macro:

[pre]

Sub NameSheets()

For Each ws In ThisWorkbook.Worksheets
ws.Name = ws.Range("A6").Value
Next

End Sub

[/pre]
Note that there is no error checks in this, so it's relying on the fact that all values in A6 are valid sheet names, and no duplicates.

 

[santoshsapre]

Thanks u sir,


i agree with u, as am very much new to vb coding, i am trying to learn something new from excel guru like you all.


Thanks once again..will trouble you again for help.

 

[SirJB7]

@Sanstoshapre


Hi!

I see you got a lifeguard from Luke M, so there's no need of my buoyancy jacket.

You'd surely get helped in your VBA and macro learning process if you are willing to take this path. My humble advise is to read, write, try, rewrite, loop, and hopefully succeed. Eventually you can contact Chandoo for available courses on VBA.

As I stated before, if you're going to use macros everywhere and for every task, you must first learn how to develop them. It's safer for you, at last.

Regards!


@Luke M


Hi!

You arrived first. Glad for that, thank you.

Regards!