Sheet Name to Cell Value

  • Thread starter Thread starter halem2
  • Start date Start date
H

halem2

Hi:

have a very large workbook with about 200 sheets. Each sheet has a
name. I need to copy the sheet name to cell B7 on that sheet for every
sheet. Does any one has a code for this? I was tryng to use

ActiveSheet.Range("B7").Text = ActiveSheet.Name

but I can't get it to work.

:confused:
 
Hi Halem,

Try:

'=============>>
Public Sub Tester()
Dim SH As Worksheet

For Each SH In ThisWorkbook.Worksheets
SH.Range("B7").Value = SH.Name
Next SH

End Sub
'<<=============
 
Option Explicit

Sub SheetNames()

Dim wks As Worksheet

For Each wks In Worksheets
wks.Range("B7").Value = wks.Name
Next wks

End Sub


Mark
 
Here you go...

sub SheetNames
dim wks as worksheet

for each wks in worksheets
wks.range("B7").value = wks.name
next wks
end sub
 
Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
With wks
.Range("B7").Value = "'" & .Name
End With
Next wks
End Sub

But if the sheets can change name, maybe a formula would be better:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim myFormula As String

myFormula _
= "=MID(CELL(""filename"",a1),FIND(""]"",CELL(""filename"",a1))+1,255)"

For Each wks In ActiveWorkbook.Worksheets
With wks
.Range("B7").Formula = myFormula
End With
Next wks
End Sub

The formula will reevaluate when the worksheet name changes.

(The workbook has to be saved at least once for the formula to work, though.)
 
Back
Top