Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • 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
 
TimCotteeCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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