Activating Sheet from a List

[Guest]

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!

 

[Guest]

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

 

[Guest]

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

Magnivy

 

[Guest]

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?

 

[Guest]

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!

 

[DS-NTE]

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

Why do you use offset?????


knut

 

[Dave Peterson]

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

 

[Guest]

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

 

[Guest]

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.

 

[Dave Peterson]

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

 

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

 

[Guest]

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

Magnivy

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.