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
Solved

cell to equal tab name on all sheets active or inactive

Posted on 2013-01-11
3
496 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to code SharePoint 2013 online 4 80
Windows 10 powershell/cmd/bash alternatives? 11 71
What is Ransomware? 16 87
Install both Office 2010 and 2013 4 46
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
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.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

860 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