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