Give a sheet name from a Cell

[Hardeep Kanwar]

Hi! Experts

http://www.mrexcel.com/forum/showthread.php?t=403613

Is it possible to give a Name to a Sheet from a Cell.

Assuming in Column A i have Sheets Names

Worksheets
CZ001
CZ002
CZ003
CZ004
CZ006
CZ007
CZ008
CZ009
CZ012
CZ014
CZ015
CZ016
CZ017



Now i want to give the Name to every Sheets depending on these Cells

Sheet1= CZ001, Sheet2= CZ002 and so on.

If Possible with Formulas otherwise Macro or Code will be Secondly Options
for me.

Coz, sometimes i have to work on other Systems or Computers.

So, its not Possible person like me who is totally stupid in Macros to use
Codes

So, thats why i always Prefers Formulas not Macros or Codes or VBA

Thanks in Advance

Hardeep kanwar

 

[Jacob Skaria]

Hi Hardeep

You will need to use a macro to achieve this..Try the below and feedback.
Select the cells with sheet names and run the macro...

Sub NameSheets()
Dim intTemp As Integer
Dim rngTemp As Range
Set rngTemp = Selection
If rngTemp.Count > Sheets.Count Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Sheets.Count), _
Count:=(rngTemp.Count - ActiveWorkbook.Sheets.Count)
End If
For Each cell In rngTemp
intTemp = intTemp + 1
ActiveWorkbook.Sheets(intTemp).Name = cell.Text
Next
End Sub

PS:Set the Security level to low/medium in (Tools|Macro|Security). From
workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and
paste the below code. Save. Get back to Workbook. Run macro from
Tools|Macro|Run <selected macro()>

If this post helps click Yes

 

[T. Valko]

There is no need to cross post. The level of responder expertise at MRExcel
is quite capable of providing a solution and has.

If Possible with Formulas

This is not possible using formulas.

 

[Hardeep Kanwar]

Thanks Jacob Skaria

Hi Hardeep

You will need to use a macro to achieve this..Try the below and feedback.
Select the cells with sheet names and run the macro...

Sub NameSheets()
Dim intTemp As Integer
Dim rngTemp As Range
Set rngTemp = Selection
If rngTemp.Count > Sheets.Count Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Sheets.Count), _
Count:=(rngTemp.Count - ActiveWorkbook.Sheets.Count)
End If
For Each cell In rngTemp
intTemp = intTemp + 1
ActiveWorkbook.Sheets(intTemp).Name = cell.Text
Next
End Sub

PS:Set the Security level to low/medium in (Tools|Macro|Security). From
workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and
paste the below code. Save. Get back to Workbook. Run macro from
Tools|Macro|Run <selected macro()>

If this post helps click Yes

 

[Hardeep Kanwar]

I was thinking its possible with formulas

Any way Thanks for the Help

Regards

Hardeep Kanwar

 

[Rick Rothstein]

Formulas can take no actions other than to display its result in the cell it
is located in.