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

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
'<<=============


---
Regards,
Norman


"halem2" wrote in
message ...

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

Option Explicit

Sub SheetNames()

Dim wks As Worksheet

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

End Sub


Mark


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

Here you go...

sub SheetNames
dim wks as worksheet

for each wks in worksheets
wks.range("B7").value = wks.name
next wks
end sub


--
HTH...

Jim Thomlinson


"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

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

worked like a charm! thanks a million.
:) :) ;) ;)


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