Give a sheet name from a Cell

  • Thread starter Thread starter Hardeep Kanwar
  • Start date Start date
H

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
 
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
 
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
Click to expand...

This is not possible using formulas.
 
Thanks Jacob Skaria



Jacob Skaria said:
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
Click to expand...
 
I was thinking its possible with formulas

Any way Thanks for the Help

Regards

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