Sheet Name to Cell Value

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

[Norman Jones]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Mark Driscol]

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

[Jim Thomlinson]

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

[Dave Peterson]

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

[halem2]

worked like a charm! thanks a million.