Solved

cell to equal tab name on all sheets active or inactive

Posted on 2013-01-11
3
500 Views
Last Modified: 2013-01-12
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?
0
Comment
Question by:rbminchew
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 38769884
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
 
LVL 47

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 38770125
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
 
LVL 47

Expert Comment

by:Martin Liss
ID: 38771180
Glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
This article was originally published on Monitis Blog, you can check it here . If you have responsibility for software in production, I bet you’d like to know more about it. I don’t mean that you’d like an extra peek into the bowels of the sourc…
Viewers will learn how to use the Hootsuite Dashboard.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question