Using Worksheet Name

[Akhil Goel]

I am having multiple worksheets in an Excel file and now I want to use the
name of each worksheet in a new worksheet in the same file.

Is there a formula which can copy the name of worksheet into a Cell of
another worksheet

 

[Pete_UK]

This article from Bob Phillips' site covers what you want:

http://www.xldynamic.com/source/xld.xlFAQ0002.html

Hope this helps.

Pete

 

[Bob Phillips]

Worksheets(2).Range("A1").Value2 = Worksheets(1).Name

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim iCtr As Long
Dim rCtr As Long

rCtr = 0
For iCtr = 1 To Sheets.Count
If Worksheets(iCtr).Name = Worksheets("NewWorksheetname").Name Then
'skip it
Else
rCtr = rCtr + 1
With Worksheets("NewWorksheetname")
With .Cells(rCtr, "A")
.NumberFormat = "@" 'text
.Value = Sheets(iCtr).Name
End With
End With
End If
Next iCtr

End Sub