?
Solved

cell to equal tab name on all sheets active or inactive

Posted on 2013-01-11
3
Medium Priority
?
501 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 49

Accepted Solution

by:
Martin Liss earned 2000 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 49

Expert Comment

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

Marty - MVP 2009 to 2012
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
All of the resources available today make learning a new digital media easier than ever-- if you know where to begin. This is a clear, simple guide to a few of the basic digital art mediums and how to begin learning them on your own.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

752 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