Insert Worksheet Names

F

Filo

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
 
H

Halim

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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top