A Quickie

M

missymissy

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
 
M

missymissy

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
 
J

Jay

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

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.
 
D

Dave Peterson

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
 
D

Desert Piranha

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 said:
All I need to know is how do I name a cell after a tab? i.e.

cell a2 = tab name
Click to expand...

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