Creating an array based on worksheet index

[Guest]

I'm trying to create an array based on the names of the worksheets in a
workbook, using the index as the variable.

I keep getting a "constant" error, can I create a array this way?



Sub test2()

Dim wrksht(num)
Dim num As Byte
Dim WS As Worksheet

num = ActiveWorkbook.Worksheets.Count
For Each WS In Worksheets
wrksht(num) = WS.name + 1
Range("A10").Formula = wrksht(num)
ActiveCell.Offset(1, 0).Select
Next WS

End Sub


THANK YOU!!

 

[Don Guillett]

try this
Sub sheetnames()
For Each WS In Sheets
WS.Range("a10") = WS.Index
Next
End Sub

 

[Guest]

The code I have I know is a little on the confusing side.

This should be an array that stores the worksheet name.

I'm trying to get the array to be variable based on the workbook index.

 

[Tushar Mehta]

You might want to explain what are you trying to do *without* including
the how. Do you want the names of all worksheets in certain cells in a
specific worksheet? Or, something else?

What you are doing is violating a bunch of programming and mathematical
rules. Even if you got it to work, your results would be that a
different cell on each worksheet would contain the name of that
worksheet.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Myrna Larson]

There are several problems:

1) Dim Wrksht(num)
Num must be a constant, known at the time the code is compiled.
It can't be the name of a variable.
2) Wrksht(Num) = WS.Name + 1
The worksheet name is a string. You can't add 1 to text.
3) Same line:
You keep putting the name in the same (last) element of the array,
overwriting what was there before.
4) If your point is to put the names onto the worksheet, one
at a time, there's no point in also saving them in an array.
See 2nd routine.

Sub test2()
Dim wrksht() As String
Dim num As Long
Dim WS As Worksheet
Dim Sh As Long

num = ActiveWorkbook.Worksheets.Count
Redim wrksht(1 to num)
Sh = 0
For Each WS In Worksheets
Sh = Sh + 1
wrksht(Sh) = WS.name
Range("A10").Offset(Sh - 1, 0).Formula = wrksht(Sh)
Next WS
End Sub

OR, dispense with the array:

Sub test2()
Dim S As Long
For S = 1 To ActiveWorkbook.Worksheets.Count
Range("A10").Offset(S - 1, 0).Value = Worksheets(S).Name
Next S
End Sub

 

[Guest]

Trust me I know there are "issues" with the code... it was a "work" in
progress.

But you did answer the most important question, I need to have a constant
for the array variable.

The reason for populating the cells with the worksheet names was to verify
that the array was being populated with the correct data.