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.

Load an array with Sheet names
 

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.

Load an array with Sheet names
 

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.

Load an array with Sheet names
 

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" wrote in message
...
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.

Load an array with Sheet names
 

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" wrote in message
...
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.

Load an array with Sheet names
 

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" wrote in message
...
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.

Load an array with Sheet names
 

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" wrote in message
...
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" wrote in message
...
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.