How to copy the sheet name to a column on the same sheet?

I am using this code:

Function wrksht() as Variant
wrksht = Application.Caller.Parent.Name
End function

I am continuously being thrown

run time error '424' object not found

I have a workbook with several sheets, each having a date on it.

I want to populate the first column on every sheet with its sheet name.

3 Answers

Function wrksht() as String wrksht = ActiveSheet.Name
End function

UPDATE

Function DoIt() Dim sh As Worksheet For Each sh In ActiveWorkbook.Sheets sh.Range("A1") = sh.Name Next
End Function

The above will work, but I have an easier solution:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Paste this value (it is a formula, not VBA code) into the cell you wish to populate with the sheet name, and it will magically appear.

Try this

Sub my_macro
On error resume next
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets ws.Range("A1") = ws.Name
Next
End sub