Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a list of Sheet names
What would be the vb code to create a list of the sheet names in an existing
sheet? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a list of Sheet names
Al
This macro places all the sheet names in Column A of a sheet named "ExistingSheet" starting in A1. HTH Otto Sub ListShtNames() Dim Dest As Range Dim ws As Worksheet With Sheets("ExistingSheet") Set Dest = .Range("A1") For Each ws In ThisWorkbook.Worksheets Dest = ws.Name Set Dest = Dest.Offset(1) Next ws End With End Sub "Al" wrote in message ... What would be the vb code to create a list of the sheet names in an existing sheet? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a list of Sheet names
On Sep 30, 12:53*pm, Al wrote:
What would be the vb code to create a list of the sheet names in an existing sheet? This should list the sheet names, starting in cell A1 of the active sheet and going down column A Sub test() Dim i As Integer For i = 1 To Sheets.Count Cells(i, 1).Value = Sheets(i).Name Next i End Sub Good luck Ken Norfolk, Va |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a list of Sheet names
How about
Sub CreateSheetsList() Dim wkbk As Excel.Workbook Dim wksht As Excel.Worksheet Dim sheetsCount As Long Dim sheetNames() As Variant Dim i As Long Set wkbk = ActiveWorkbook Set wksht = ActiveSheet sheetsCount = wkbk.Sheets.Count ReDim sheetNames(1 To sheetsCount) For i = 1 To sheetsCount sheetNames(i) = wkbk.Sheets(i).Name Next i wksht.Range(wksht.Range("A1"), wksht.Range("A" & sheetsCount)).Value = Application.Transpose(sheetNames) End Sub --JP On Sep 30, 12:53*pm, Al wrote: What would be the vb code to create a list of the sheet names in an existing sheet? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Create a list of Sheet names
Here's an array function that will return the names of the worksheets
to a range of cells. Function SheetNames() As Variant Dim WB As Workbook Dim WS As Worksheet Dim SNames() As String Dim Trans As Boolean Dim L As Long Dim N As Long Dim R As Range Set R = Application.Caller Set WB = R.Parent.Parent If R.Columns.Count = 1 Then L = R.Rows.Count Trans = True Else L = R.Columns.Count Trans = False End If ReDim SNames(1 To L) For N = 1 To Application.Min(L, WB.Worksheets.Count) SNames(N) = WB.Worksheets(N).Name Next N If Trans Then SheetNames = Application.Transpose(SNames) Else SheetNames = SNames End If End Function Select the range of cells that are to receive the sheet names. Then, type =SheetNames() and press CTRL SHIFT ENTER instead of just ENTER. It will not work if you press ENTER rather than CTRL SHIFT ENTER. If the range of cells is greater than the number of worksheets, the cells at the end of the range will be empty. If the number of selected cells is less than the number of worksheets, only the first N worksheet names will be returned, where N is the number of selected cells. You can enter the function into a range of cells that spans multiple rows in a single column or in a range that spans several columns in a single row. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 30 Sep 2009 09:53:02 -0700, Al wrote: What would be the vb code to create a list of the sheet names in an existing sheet? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generate sheet names from list, assign data to summary sheet. | Excel Programming | |||
Need to create a list of sheets from a list of names in column a | Excel Programming | |||
create a random list of names from a list of names in EXCEL | Excel Worksheet Functions | |||
How do I create a list of names and addresses? | Excel Discussion (Misc queries) | |||
List Of Sheet Names & Create Hotlinks for each one | Excel Programming |