Solved

cell to equal tab name on all sheets active or inactive

Posted on 2013-01-11
3
489 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
  • 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 46

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 46

Expert Comment

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

Marty - MVP 2009 to 2012
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

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.
Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now