Hi There

All I need to know is how do I name a cell after a tab? i.e.

cell a2 = tab name

Is it me being silly or can you not do it?

thanks in advance

Do you mean name the cell or put the name of the worksheet in the cell?

If it's the second, visit Debra Dalgleish's site:
http://www.contextures.com/xlfaqFun.html#SheetName

missymissy wrote:

Hi There

All I need to know is how do I name a cell after a tab? i.e.

cell a2 = tab name

Is it me being silly or can you not do it?

thanks in advance

--

Dave Peterson

Sorry not very specific,

I mean name the cell after the tab, for instance, if I opened a new workbook
I could enter a formula which into cell a1 and the result would be:

Sheet1

The reason I am asking this is because I import data from a bespoke
database, the result is I end up with 100+ tabs each with thier individual
name, I want to highlight all tabs, insert a column and name the cell after
the tab? Are you with me?

thanks

"Dave Peterson" wrote:

Do you mean name the cell or put the name of the worksheet in the cell?

If it's the second, visit Debra Dalgleish's site:
http://www.contextures.com/xlfaqFun.html#SheetName

missymissy wrote:

Hi There

All I need to know is how do I name a cell after a tab? i.e.

cell a2 = tab name

Is it me being silly or can you not do it?

thanks in advance

--

Dave Peterson

All I need to know is how do I name a cell after a tab? i.e.

cell a2 = tab name

If you mean having the sheet name in the cell, one way is the formula:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

You have to save the file before the formula can work.

Maybe...

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim NewName As String

For Each wks In ActiveWorkbook.Worksheets
With wks
.Range("a1").Value = "'" & .name
.Range("e1").EntireColumn.Insert
End With
Next wks

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


just because I read the question wrong when I made the macro (sigh), this names
the worksheet after the value in A1 (toss it if you don't want it).


Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim NewName As String

For Each wks In ActiveWorkbook.Worksheets
With wks
NewName = .Range("a1").Value
.Range("e1").EntireColumn.Insert
On Error Resume Next
.Name = NewName
If Err.Number < 0 Then
MsgBox .Name & " was not renamed!"
Err.Clear
End If
On Error GoTo 0
End With
Next wks

End Sub


missymissy wrote:

Sorry not very specific,

I mean name the cell after the tab, for instance, if I opened a new workbook
I could enter a formula which into cell a1 and the result would be:

Sheet1

The reason I am asking this is because I import data from a bespoke
database, the result is I end up with 100+ tabs each with thier individual
name, I want to highlight all tabs, insert a column and name the cell after
the tab? Are you with me?

thanks

"Dave Peterson" wrote:

Do you mean name the cell or put the name of the worksheet in the cell?

If it's the second, visit Debra Dalgleish's site:
http://www.contextures.com/xlfaqFun.html#SheetName

missymissy wrote:

Hi There

All I need to know is how do I name a cell after a tab? i.e.

cell a2 = tab name

Is it me being silly or can you not do it?

thanks in advance

--

Dave Peterson


--

Dave Peterson

Hi,
If you put this formula on more than one sheet, you may not get the
individual sheet names, on each sheet.
'=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

This you put this formula on more than one sheet, it will give you
individual sheet names, on each sheet.
'=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Dave
Jay Wrote:
All I need to know is how do I name a cell after a tab? i.e.

cell a2 = tab name

If you mean having the sheet name in the cell, one way is the formula:
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

You have to save the file before the formula can work.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=495216