Hey There,

I have a workbook with numerous worksheets. One of the worksheets, say
Sheet1, lists the names of the other worksheets, say Sheet2 through Sheet
10,in cells A2 through A10. Each cell contains the name of only one worksheet.

I am trying to create a macro that would read the name of Sheets2 from cell
A2, select it, perform an operation on it (which I already have), select
Sheet3 from Cell A3, perform an operation on it, and so on till the operation
is performed on Sheet10.

I havent been able to come up with a macro that would work. Any help you
provide would be greatly appreciated!

Sincerely,

Magnivy!

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets, say
Sheet1, lists the names of the other worksheets, say Sheet2 through Sheet
10,in cells A2 through A10. Each cell contains the name of only one worksheet.

I am trying to create a macro that would read the name of Sheets2 from cell
A2, select it, perform an operation on it (which I already have), select
Sheet3 from Cell A3, perform an operation on it, and so on till the operation
is performed on Sheet10.

I havent been able to come up with a macro that would work. Any help you
provide would be greatly appreciated!

Sincerely,

Magnivy!

Tom, once again, you're a life saver! Thanks a million!

Magnivy

"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets, say
Sheet1, lists the names of the other worksheets, say Sheet2 through Sheet
10,in cells A2 through A10. Each cell contains the name of only one worksheet.

I am trying to create a macro that would read the name of Sheets2 from cell
A2, select it, perform an operation on it (which I already have), select
Sheet3 from Cell A3, perform an operation on it, and so on till the operation
is performed on Sheet10.

I havent been able to come up with a macro that would work. Any help you
provide would be greatly appreciated!

Sincerely,

Magnivy!

Tom,

One more question about this if you dont mind. I've modified your macro
slighly to have it adjust to the number of sheets to be activated. I used the
following macro:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cell(2,1),cell(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

This formula, for some reason, generates run-time error 91, " object
variable or with block variable not set." Would you happen to know why this
error is occuring and how can I correct it?


"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets, say
Sheet1, lists the names of the other worksheets, say Sheet2 through Sheet
10,in cells A2 through A10. Each cell contains the name of only one worksheet.

I am trying to create a macro that would read the name of Sheets2 from cell
A2, select it, perform an operation on it (which I already have), select
Sheet3 from Cell A3, perform an operation on it, and so on till the operation
is performed on Sheet10.

I havent been able to come up with a macro that would work. Any help you
provide would be greatly appreciated!

Sincerely,

Magnivy!

Tom,

sorry! I see whats wrong. I mispelled Cells in
set rng .Range(cell(2,1),cell(2,1).offset(15,0))

But even when I write it correctly as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cells(2,1),cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

it generates a run-time error 9, "subscript out of range," and the following
line is highlighted:

set sh = worksheets(cell.value)

Would you happen to know whats causing the error?

Thanks a lot for your help!
"Magnivy" wrote:

Tom,

One more question about this if you dont mind. I've modified your macro
slighly to have it adjust to the number of sheets to be activated. I used the
following macro:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cell(2,1),cell(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

This formula, for some reason, generates run-time error 91, " object
variable or with block variable not set." Would you happen to know why this
error is occuring and how can I correct it?


"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets, say
Sheet1, lists the names of the other worksheets, say Sheet2 through Sheet
10,in cells A2 through A10. Each cell contains the name of only one worksheet.

I am trying to create a macro that would read the name of Sheets2 from cell
A2, select it, perform an operation on it (which I already have), select
Sheet3 from Cell A3, perform an operation on it, and so on till the operation
is performed on Sheet10.

I havent been able to come up with a macro that would work. Any help you
provide would be greatly appreciated!

Sincerely,

Magnivy!

"set rng = .Range(cells(2,1),cells(2,1).offset(15,0))"

Why do you use offset?????


knut


"Magnivy" skrev i melding
...
Tom,

sorry! I see whats wrong. I mispelled Cells in
set rng .Range(cell(2,1),cell(2,1).offset(15,0))

But even when I write it correctly as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cells(2,1),cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

it generates a run-time error 9, "subscript out of range," and the
following
line is highlighted:

set sh = worksheets(cell.value)

Would you happen to know whats causing the error?

Thanks a lot for your help!
"Magnivy" wrote:

Tom,

One more question about this if you dont mind. I've modified your macro
slighly to have it adjust to the number of sheets to be activated. I used
the
following macro:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cell(2,1),cell(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

This formula, for some reason, generates run-time error 91, " object
variable or with block variable not set." Would you happen to know why
this
error is occuring and how can I correct it?


"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets,
say
Sheet1, lists the names of the other worksheets, say Sheet2 through
Sheet
10,in cells A2 through A10. Each cell contains the name of only one
worksheet.

I am trying to create a macro that would read the name of Sheets2
from cell
A2, select it, perform an operation on it (which I already have),
select
Sheet3 from Cell A3, perform an operation on it, and so on till the
operation
is performed on Sheet10.

I haven't been able to come up with a macro that would work. Any help
you
provide would be greatly appreciated!

Sincerely,

Magnivy!

It means that the value in that cell (cell.value) isn't the name of a worksheet
in that workbook.

And watch your references. You dropped a couple of necessary dots.

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(.cells(2,1),.cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = nothing
on error resume next
set sh = worksheets(cell.value)
on error goto 0
if sh is nothing then
msgbox "Something invalid in: " & cell.address(0,0) & vblf & cell.value
else
sh.Activate
msgbox "Look at sheet " & sh.Name
end if
Next sh



Magnivy wrote:

Tom,

sorry! I see whats wrong. I mispelled Cells in
set rng .Range(cell(2,1),cell(2,1).offset(15,0))

But even when I write it correctly as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cells(2,1),cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

it generates a run-time error 9, "subscript out of range," and the following
line is highlighted:

set sh = worksheets(cell.value)

Would you happen to know whats causing the error?

Thanks a lot for your help!
"Magnivy" wrote:

Tom,

One more question about this if you dont mind. I've modified your macro
slighly to have it adjust to the number of sheets to be activated. I used the
following macro:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cell(2,1),cell(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

This formula, for some reason, generates run-time error 91, " object
variable or with block variable not set." Would you happen to know why this
error is occuring and how can I correct it?


"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets, say
Sheet1, lists the names of the other worksheets, say Sheet2 through Sheet
10,in cells A2 through A10. Each cell contains the name of only one worksheet.

I am trying to create a macro that would read the name of Sheets2 from cell
A2, select it, perform an operation on it (which I already have), select
Sheet3 from Cell A3, perform an operation on it, and so on till the operation
is performed on Sheet10.

I havent been able to come up with a macro that would work. Any help you
provide would be greatly appreciated!

Sincerely,

Magnivy!


--

Dave Peterson

Dave,

The value in the cell is a name of a worksheet. The formula works if I
indicate the range directly, without using the offset function, as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

For some reason it doesnt work if I use the offset to indicate the range
that contains the worksheet names. Would you happen to know whats causing the
problem?

Thanks!

Magnivy
"Dave Peterson" wrote:

It means that the value in that cell (cell.value) isn't the name of a worksheet
in that workbook.

And watch your references. You dropped a couple of necessary dots.

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(.cells(2,1),.cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = nothing
on error resume next
set sh = worksheets(cell.value)
on error goto 0
if sh is nothing then
msgbox "Something invalid in: " & cell.address(0,0) & vblf & cell.value
else
sh.Activate
msgbox "Look at sheet " & sh.Name
end if
Next sh



Magnivy wrote:

Tom,

sorry! I see whats wrong. I mispelled Cells in
set rng .Range(cell(2,1),cell(2,1).offset(15,0))

But even when I write it correctly as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cells(2,1),cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

it generates a run-time error 9, "subscript out of range," and the following
line is highlighted:

set sh = worksheets(cell.value)

Would you happen to know whats causing the error?

Thanks a lot for your help!
"Magnivy" wrote:

Tom,

One more question about this if you dont mind. I've modified your macro
slighly to have it adjust to the number of sheets to be activated. I used the
following macro:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cell(2,1),cell(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

This formula, for some reason, generates run-time error 91, " object
variable or with block variable not set." Would you happen to know why this
error is occuring and how can I correct it?


"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets, say
Sheet1, lists the names of the other worksheets, say Sheet2 through Sheet
10,in cells A2 through A10. Each cell contains the name of only one worksheet.

I am trying to create a macro that would read the name of Sheets2 from cell
A2, select it, perform an operation on it (which I already have), select
Sheet3 from Cell A3, perform an operation on it, and so on till the operation
is performed on Sheet10.

I haven€„¢t been able to come up with a macro that would work. Any help you
provide would be greatly appreciated!

Sincerely,

Magnivy!


--

Dave Peterson

I'm using the offset so that when the number of worksheets that I want to
activate changes, the formula would adjust. In other words, when the number
of cells in column A changes, the formula would adjust to select all the
cells that have sheet names in them.

"DS-NTE" wrote:

"set rng = .Range(cells(2,1),cells(2,1).offset(15,0))"

Why do you use offset?????


knut


"Magnivy" skrev i melding
...
Tom,

sorry! I see whats wrong. I mispelled Cells in
set rng .Range(cell(2,1),cell(2,1).offset(15,0))

But even when I write it correctly as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cells(2,1),cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

it generates a run-time error 9, "subscript out of range," and the
following
line is highlighted:

set sh = worksheets(cell.value)

Would you happen to know whats causing the error?

Thanks a lot for your help!
"Magnivy" wrote:

Tom,

One more question about this if you dont mind. I've modified your macro
slighly to have it adjust to the number of sheets to be activated. I used
the
following macro:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cell(2,1),cell(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

This formula, for some reason, generates run-time error 91, " object
variable or with block variable not set." Would you happen to know why
this
error is occuring and how can I correct it?


"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets,
say
Sheet1, lists the names of the other worksheets, say Sheet2 through
Sheet
10,in cells A2 through A10. Each cell contains the name of only one
worksheet.

I am trying to create a macro that would read the name of Sheets2
from cell
A2, select it, perform an operation on it (which I already have),
select
Sheet3 from Cell A3, perform an operation on it, and so on till the
operation
is performed on Sheet10.

I haven't been able to come up with a macro that would work. Any help
you
provide would be greatly appreciated!

Sincerely,

Magnivy!

Try running that suggested code. It'll tell you what's in the cell that has
trouble (and its address).

I'm betting that there is a spelling difference--maybe an extra space
(leading/trailing/embedded????).

Magnivy wrote:

Dave,

The value in the cell is a name of a worksheet. The formula works if I
indicate the range directly, without using the offset function, as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

For some reason it doesnt work if I use the offset to indicate the range
that contains the worksheet names. Would you happen to know whats causing the
problem?

Thanks!

Magnivy
"Dave Peterson" wrote:

It means that the value in that cell (cell.value) isn't the name of a worksheet
in that workbook.

And watch your references. You dropped a couple of necessary dots.

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(.cells(2,1),.cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = nothing
on error resume next
set sh = worksheets(cell.value)
on error goto 0
if sh is nothing then
msgbox "Something invalid in: " & cell.address(0,0) & vblf & cell.value
else
sh.Activate
msgbox "Look at sheet " & sh.Name
end if
Next sh



Magnivy wrote:

Tom,

sorry! I see whats wrong. I mispelled Cells in
set rng .Range(cell(2,1),cell(2,1).offset(15,0))

But even when I write it correctly as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cells(2,1),cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

it generates a run-time error 9, "subscript out of range," and the following
line is highlighted:

set sh = worksheets(cell.value)

Would you happen to know whats causing the error?

Thanks a lot for your help!
"Magnivy" wrote:

Tom,

One more question about this if you dont mind. I've modified your macro
slighly to have it adjust to the number of sheets to be activated. I used the
following macro:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cell(2,1),cell(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

This formula, for some reason, generates run-time error 91, " object
variable or with block variable not set." Would you happen to know why this
error is occuring and how can I correct it?


"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets, say
Sheet1, lists the names of the other worksheets, say Sheet2 through Sheet
10,in cells A2 through A10. Each cell contains the name of only one worksheet.

I am trying to create a macro that would read the name of Sheets2 from cell
A2, select it, perform an operation on it (which I already have), select
Sheet3 from Cell A3, perform an operation on it, and so on till the operation
is performed on Sheet10.

I haven€„¢t been able to come up with a macro that would work. Any help you
provide would be greatly appreciated!

Sincerely,

Magnivy!


--

Dave Peterson


--

Dave Peterson

ps. Your range isn't the same in your two examples, either.

..Range(.cells(2,1),.cells(2,1).offset(15,0))
is A2:A17.

Maybe that should be the next test.


Dave Peterson wrote:

Try running that suggested code. It'll tell you what's in the cell that has
trouble (and its address).

I'm betting that there is a spelling difference--maybe an extra space
(leading/trailing/embedded????).

Magnivy wrote:

Dave,

The value in the cell is a name of a worksheet. The formula works if I
indicate the range directly, without using the offset function, as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

For some reason it doesnt work if I use the offset to indicate the range
that contains the worksheet names. Would you happen to know whats causing the
problem?

Thanks!

Magnivy
"Dave Peterson" wrote:

It means that the value in that cell (cell.value) isn't the name of a worksheet
in that workbook.

And watch your references. You dropped a couple of necessary dots.

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(.cells(2,1),.cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = nothing
on error resume next
set sh = worksheets(cell.value)
on error goto 0
if sh is nothing then
msgbox "Something invalid in: " & cell.address(0,0) & vblf & cell.value
else
sh.Activate
msgbox "Look at sheet " & sh.Name
end if
Next sh



Magnivy wrote:

Tom,

sorry! I see whats wrong. I mispelled Cells in
set rng .Range(cell(2,1),cell(2,1).offset(15,0))

But even when I write it correctly as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cells(2,1),cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

it generates a run-time error 9, "subscript out of range," and the following
line is highlighted:

set sh = worksheets(cell.value)

Would you happen to know whats causing the error?

Thanks a lot for your help!
"Magnivy" wrote:

Tom,

One more question about this if you dont mind. I've modified your macro
slighly to have it adjust to the number of sheets to be activated. I used the
following macro:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cell(2,1),cell(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

This formula, for some reason, generates run-time error 91, " object
variable or with block variable not set." Would you happen to know why this
error is occuring and how can I correct it?


"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets, say
Sheet1, lists the names of the other worksheets, say Sheet2 through Sheet
10,in cells A2 through A10. Each cell contains the name of only one worksheet.

I am trying to create a macro that would read the name of Sheets2 from cell
A2, select it, perform an operation on it (which I already have), select
Sheet3 from Cell A3, perform an operation on it, and so on till the operation
is performed on Sheet10.

I haven€„¢t been able to come up with a macro that would work. Any help you
provide would be greatly appreciated!

Sincerely,

Magnivy!


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

Dave, your macro works for me. Thanks a lot for your help!

Magnivy

"Dave Peterson" wrote:

ps. Your range isn't the same in your two examples, either.

..Range(.cells(2,1),.cells(2,1).offset(15,0))
is A2:A17.

Maybe that should be the next test.


Dave Peterson wrote:

Try running that suggested code. It'll tell you what's in the cell that has
trouble (and its address).

I'm betting that there is a spelling difference--maybe an extra space
(leading/trailing/embedded????).

Magnivy wrote:

Dave,

The value in the cell is a name of a worksheet. The formula works if I
indicate the range directly, without using the offset function, as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

For some reason it doesnt work if I use the offset to indicate the range
that contains the worksheet names. Would you happen to know whats causing the
problem?

Thanks!

Magnivy
"Dave Peterson" wrote:

It means that the value in that cell (cell.value) isn't the name of a worksheet
in that workbook.

And watch your references. You dropped a couple of necessary dots.

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(.cells(2,1),.cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = nothing
on error resume next
set sh = worksheets(cell.value)
on error goto 0
if sh is nothing then
msgbox "Something invalid in: " & cell.address(0,0) & vblf & cell.value
else
sh.Activate
msgbox "Look at sheet " & sh.Name
end if
Next sh



Magnivy wrote:

Tom,

sorry! I see whats wrong. I mispelled Cells in
set rng .Range(cell(2,1),cell(2,1).offset(15,0))

But even when I write it correctly as follows:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cells(2,1),cells(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

it generates a run-time error 9, "subscript out of range," and the following
line is highlighted:

set sh = worksheets(cell.value)

Would you happen to know whats causing the error?

Thanks a lot for your help!
"Magnivy" wrote:

Tom,

One more question about this if you dont mind. I've modified your macro
slighly to have it adjust to the number of sheets to be activated. I used the
following macro:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range(cell(2,1),cell(2,1).offset(15,0))
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

This formula, for some reason, generates run-time error 91, " object
variable or with block variable not set." Would you happen to know why this
error is occuring and how can I correct it?


"Tom Ogilvy" wrote:

Dim rng as Range, cell as Range
Dim sh as Worksheet
with worksheets("sheet1")
set rng = .Range("A1:A10")
End With
for each cell in rng
set sh = worksheets(cell.value)
sh.Activate
msgbox "Look at sheet " & sh.Name
Next

--
Regards,
Tom Ogilvy

"Magnivy" wrote:

Hey There,

I have a workbook with numerous worksheets. One of the worksheets, say
Sheet1, lists the names of the other worksheets, say Sheet2 through Sheet
10,in cells A2 through A10. Each cell contains the name of only one worksheet.

I am trying to create a macro that would read the name of Sheets2 from cell
A2, select it, perform an operation on it (which I already have), select
Sheet3 from Cell A3, perform an operation on it, and so on till the operation
is performed on Sheet10.

I haven€„¢t been able to come up with a macro that would work. Any help you
provide would be greatly appreciated!

Sincerely,

Magnivy!


--

Dave Peterson


--

Dave Peterson

--

Dave Peterson