Load an array with Sheet names

[S G Booth]

varr is a Variant array. I wish to load it with names of sheets in the
activeworkbook.
I have:

If Global_PrintAllBooks_Sheets = True Then
ReDim varr(1 To Worksheets.Count)
For i = 1 To UBound(varr)
varr(i) = ws.Name
i = i + 1
Next

I receive run time error 91 (Object variable or With block variable not set)
on varr(i) = ws.Name

Why is this please?

Regards.

 

[Bob]

Be sure you have: Set ws = Worksheets and change varr(i) = ws.Name to
read varr(i) = ws(i).Name. Also, take out: i = i + 1 (the Next satement
causes i to increment by one automatically)

 

[Jim Thomlinson]

I am not too sure why you would like to load an array with the sheet names.
The sheets are a collection and the names can be accessed with a for next
loop.

dim wks as worksheet

for each wks in worksheets
msgbox wks.name
next wks

If you still need the array you can populate it from within the loop... but
I am not sure that it is necessary.

HTH

 

[S G Booth]

Had ws already Dimmed as Worksheet, so this seems to work:

ReDim varr(1 To Worksheets.Count)
For i = 1 To UBound(varr)
varr(i) = Worksheets(i).Name
Next

Many thanks for your help.
Regards.

 

[S G Booth]

I appreciate that, thanks. It's just that later in the routine
a fairly complicated "print" routine runs. It seems fairly
robust, so far, and revolves around the sheet names held
by varr.
This later routine populates varr via a listbox ( ie if selected, then add
to varr) and thus allows a user to select
sheets to be entered into the print routine. What I have yet to accommodate
is for all sheets to be worked with.

Rather than mess with the later code, I hoped to populate varr with all the
sheet names.

Regards and thanks.

 

[Bob]

Just for future reference: If you wanted to use ws, don't Dim it as a
Worksheet. Dim it as an Object, then Set ws = Worksheets (<<plural since you
want the collection)

 

[Tom Ogilvy]

while Stuart has already arrived at a solution and you are providing future
adivce,
Why dim it as object if you know it is going to be worksheets?

Dim ws As Worksheets

should do nicely.