Load an array with Sheet names

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.

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)
"S G Booth" wrote:

> 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.
>
>
>

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" wrote:

> 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.
>
>
>

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.

"Bob" <Bob@discussions.microsoft.com> wrote in message
news:F119C44F-708B-432C-AD7C-49E83C1197BA@microsoft.com...
> 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)
> "S G Booth" wrote:
>
>> 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.
>>
>>
>>

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.

"Jim Thomlinson" <JimThomlinson@discussions.microsoft.com> wrote in message
news:9EC2DBFB-FA89-41CA-A293-1E146453BDD4@microsoft.com...
>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" wrote:
>
>> 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.
>>
>>
>>

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)

"S G Booth" wrote:

> 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.
>
> "Bob" <Bob@discussions.microsoft.com> wrote in message
> news:F119C44F-708B-432C-AD7C-49E83C1197BA@microsoft.com...
> > 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)
> > "S G Booth" wrote:
> >
> >> 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.
> >>
> >>
> >>
>
>
>

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.

--
Regards,
Tom Ogilvy

"Bob" <Bob@discussions.microsoft.com> wrote in message
news:FCEB9985-CBE9-4143-B4B3-A6804F83EE5C@microsoft.com...
> 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)
>
> "S G Booth" wrote:
>
> > 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.
> >
> > "Bob" <Bob@discussions.microsoft.com> wrote in message
> > news:F119C44F-708B-432C-AD7C-49E83C1197BA@microsoft.com...
> > > 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)
> > > "S G Booth" wrote:
> > >
> > >> 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.
> > >>
> > >>
> > >>
> >
> >
> >