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


halem2 wrote:

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.



--
halem2
------------------------------------------------------------------------
halem2's Profile: http://www.excelforum.com/member.php...fo&userid=9930
View this thread: http://www.excelforum.com/showthread...hreadid=556975

--

Dave Peterson