cell to equal tab name on all sheets active or inactive

Is there a formula that when a macro or vba is run that creates new worksheets in a workbook to populate a cell with the tab name for all sheets not just the active one in excel?
rbminchewAsked:
Who is Participating?
 
Martin LissConnect With a Mentor Older than dirtCommented:
Here is code for the Workbook_NewSheet event in the code page for 'ThisWorkbook'. It will change cell A1 on Sheet1 to show the names of all existing sheets every time a sheet is added.


Private Sub Workbook_NewSheet(ByVal Sh As Object)

Dim ws As Worksheet

Worksheets("sheet1").Cells(1, 1).ClearContents
For Each ws In ThisWorkbook.Worksheets
    Worksheets("sheet1").Cells(1, 1).Value = Worksheets("sheet1").Cells(1, 1).Value & vbCrLf & ws.Name
Next ws

End Sub

Open in new window

0
 
SteveCommented:
What is it you are trying to do... there is VBA code which would allow you to fill the sheetnames into cells... for example:

Sub Names_of_worksheets()

Dim ws As Worksheet
Dim ws2 As Worksheet
Dim x As Long

Set ws2 = Sheets.Add

For Each ws In ThisWorkbook.worksheets
    x = ws.Index
    ws2.Cells(x, "A") = ws.Name
Next ws

End Sub

Open in new window

0
 
Martin LissOlder than dirtCommented:
Glad I was able to help.

Marty - MVP 2009 to 2012
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.