Sheet names into an array

D

decafme

New Member
Joined
Feb 18, 2004
Messages
3
Hi guys,

I know this must be possible, but I can't think how to approach it.

I want to be able to allocate the names of each worksheet in a workbook to an vba array, without knowing how many sheets are in the workbook when the code is run, or what the sheet names are.

Is this possible, and how would I go about doing it? Let me know if this is not specific enough, and I'll try to go into more detail.

Thanks
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
This should work for you:

Code: 
Sub sheetray()
    Dim SNarray, i
    ReDim SNarray(1 To Sheets.count)
    For i = 1 To Sheets.count
        SNarray(i) = ThisWorkbook.Sheets(i).Name
Debug.Print SNarray(i)
    Next
End Sub

HTH
 
Upvote 0
This might help - it will list the sheets on the active sheet as well as put them in an array...

Sub Macro1()
Dim ShtNames() As String
ReDim ShtNames(1 To ActiveWorkbook.Sheets.Count)
For i = 1 To Sheets.Count
ShtNames(i) = Sheets(i).Name
Next i

For i = 1 To Sheets.Count
Cells(i, 1) = ShtNames(i)
Next i
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
VBA to protect/unprotect sheet...
Replies
6
Views
259
jkpieterse
jkpieterse
P
  • Question Question
TypeScript (or other solutions) for modifying given list of cells (and similar problems)
Replies
0
Views
256
Pingaware
P
R
  • Question Question
Smarter alternative to Name Manager & Indirect Formula?
Replies
1
Views
466
RoryA
RoryA
P
  • Question Question
Checking for Worksheets that already exist
2
Replies
11
Views
329
Logit
Logit
N
  • Question Question
Transferring Data Between Two Open Workbooks
Replies
6
Views
159
NorthbyNorthwest
N

Forum statistics

Threads
1,224,602
Messages
6,179,843
Members
452,948
Latest member
UsmanAli786

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top