Solved

# Get worksheet names in Excel

Posted on 2003-10-27
293 Views
What code would you use to get a list of every worksheet name in an Excel Speadsheet?
0
Question by:LorangerG
[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
• 4

LVL 44

Accepted Solution

bruintje earned 125 total points
ID: 9626891
in vba you could try something like

Option Explicit

Sub ListNames()
Dim sh As Worksheet
Dim i As Integer
i = 1
For Each sh In ThisWorkbook.Sheets
Sheets(1).Range("A" & i) = sh.Name
i = i + 1
Next sh
End Sub
0

LVL 43

Expert Comment

ID: 9626893
Hi LorangerG,

Dim shtToList As string
Dim strSheets As String
For Each shtToList In Worksheets
strSheets = strSheets & shtToList.Name & vbLf
Next
Msgbox strSheets

Should do it.

Tim Cottee MCSD, MCDBA, CPIM
Brainbench MVP for Visual Basic
http://www.brainbench.com
0

LVL 44

Expert Comment

ID: 9626899
which will list the names on the first sheet in your workbook starting in cell A1 till the number of sheets in your workbook
0

LVL 44

Expert Comment

ID: 9626900
which will list the names on the first sheet in your workbook starting in cell A1 till the number of sheets in your workbook
0

LVL 44

Expert Comment

ID: 9626922
in a function you could be passing in the workbook object and retrieve an array of names

Public Function GetWshNames(ByVal myxl As Object) As String()
Dim sh As Object
Dim sOut() As String
Dim i As Integer

ReDim sOut(myxl.Workbooks(1).Sheets.Count)

i = 0
For Each sh In myxl.Workbooks(1).Sheets
sOut(i) = sh.Name
i = i + 1
Next sh

GetWshNames = sOut

End Function
0

Author Comment

ID: 9627006
Man you guys are quick! Both answers were great bruintje was first.
0

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

passing parameter in sql procedure 9 70
How does CurrentUser work? 10 44
fso.FolderExists("\\server\HiddenFolder\$") 4 98