• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

Get worksheet names in Excel

What code would you use to get a list of every worksheet name in an Excel Speadsheet?
0
LorangerG
Asked:
LorangerG
  • 4
1 Solution
 
bruintjeCommented:
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
 
TimCotteeHead of Software ServicesCommented:
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
 
bruintjeCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bruintjeCommented:
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
 
bruintjeCommented:
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
 
LorangerGAuthor Commented:
Man you guys are quick! Both answers were great bruintje was first.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now