Insert Worksheet Names

[Guest]

What is the code to paste the worksheet name in cell "C3" of each worksheet
in the workbook? I tried the following, but it didn't work. Thank you.

Sub insertname()
Dim Rng As Range
Dim n As Integer
Dim y as integer

n = Worksheets.Count
For y = 1 To n
Worksheets(y).Select
Set Rng = Range("A1")
Range("A1").Select
ActiveCell.Offset(0, 3).Value = Sheet.Name
Next y

end sub

 

[Guest]

Hi Filo,

You can try this one:

Sub insertname()
Dim Wks as Worksheet
For each Wks in activeworkbook.sheets
Wks.Range("C3") = Wks.name
Next Wks
End Sub

 

[David McRitchie]

Hi Filo,
Halim's rewrite is, of course, better because the activecell
and the activesheet are not changed so there is no screen
refresh so that is the kind of coding that you want to use.

But yours would probably work had you used
ActiveSheet.Range("C3") = ActiveSheet.Name