Selecting sheet with VB

[RobN]

(I posted this in the office.misc newsgroup by mistake)

I've used the following to try and have VB select a sheet dependant on the
value in d2.
Dim ss As Variant
Set ss = Sheet60.Range("d2")

How do I apply that now to select that sheet? I thought something like
sheet(ss).select would do it, but it doesn't work

Sheets(ss).select does work, but doesn't go to the correct sheet number,
whereas Sheet(ss) brings up a "Sub or Function not defined" error.

Rob

 

[Bob Phillips]

ss.Select

but you rarely need to select the sheet, just use the ss variable.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Try,

Dim ss As String
ss = Sheets("Sheet60").Range("d2").Value
Sheets(ss).Select

Mike

 

[FSt1]

hi
you're trying to select too much at once.
to select a range, the sheet that has the range must be selected first.
dim ws as worksheet
dim ss as range
set ws = sheets("sheet60")
set ss = sheets("sheet60").range("D2")
ws.activate
ss.select

regards
FSt1

 

[RobN]

Mike,

Now says Subscript out of range.

Rob

Try,

Dim ss As String
ss = Sheets("Sheet60").Range("d2").Value
Sheets(ss).Select

Mike

 

[Mike H]

Hi,

Then you don't have a Sheet60 or you don't have a sheet that matches what
you have typed in D2 of Sheet60. Check for spelling and spaces.

Mike

 

[RobN]

Bob,

I tried the following without success.
Dim ss As Variant
Set ss = Sheet60.Range("d2")
ss.Select

error: Select method of range class failed.

Rob

 

[RobN]

I think this is getting somewhere, but not quite.

What I have now is:

Dim ws As Worksheet
Dim ss As Range
Set ws = Sheets("SheetNames")
Set ss = Sheets("SheetNames").Range("D2")
then some other code, and eventually
ws.activate
ss.select

It works to the point of activating the sheet called "SheetNames" by the
command ws.activate, but it totally ignores the command ss.select.
When I hover my mouse over the ss variable before the procedure ends, it
shows "ss = sheet6" which is what I expect, but it doesn't activate that
sheet.
The formula in the sheets called "SheetNames" in cell D2 is
="Sheet"&VLOOKUP(TRUE,A260,4,FALSE) and the result is Sheet6
If I change that to simply VLOOKUP(TRUE,A260,4,FALSE) the result is 6.
But that doesn't work either.

Rob

 

[RobN]

Fraid I do!

I copied and pasted it here from the VBA projects window ---> Sheet60. It's
full name is Sheet60 (SheetNames).
However, I did try amending your Sheets("Sheet60").Range("d2").Value
to
Sheets("SheetNames").Range("d2").Value, but that didn't work either.

I'm also a bit confused with the command Sheet or Sheets. Which one should
it be?

Rob

 

[FSt1]

hi
did it select cell D2? that was all it was suppost to do.
activating sheet 6 as the result of a formala is another whole code ball game.

regards
FSt1

 

[Bob Phillips]

sorry, I meant

Dim ss As Variant
Set ss = Sheet60
ss.Select
ss..Range("d2").select

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H]

Hi,

If you confident you have sheets as named then i'm confused but with regard to

Sheets("SheetNames").Range("d2").Value

That won't work because it will look for a worksheet '"Sheetnames" but this
will provided 'Sheet60' is a valid sheet name and the value in D2 is a valid
sheet name

Sheets(Sheets("Sheet60").Range("d2").Value).Select

Mike

 

[Gord Dibben]

Sheets(60).Select


Gord Dibben MS Excel MVP

Fraid I do!

I copied and pasted it here from the VBA projects window ---> Sheet60. It's
full name is Sheet60 (SheetNames).
However, I did try amending your Sheets("Sheet60").Range("d2").Value
to
Sheets("SheetNames").Range("d2").Value, but that didn't work either.

I'm also a bit confused with the command Sheet or Sheets. Which one should
it be?

Rob

 

[RobN]

Thanks to all who contributed. I don't think my problem was fully
understood, but I think I've managed to compile a solution from using bits
from all the answers.

Rob

 

[RobN]

I thought I had this solved, but I find now that the procedure doesn't find
the right sheet. I don't understand because it appears the code and
formulas are correct and the sheet numbers match the sheet names, etc.
If it's not too much trouble for you or someone else, I've pasted the full
code. The code opens a userform from which one of the sheet names is
selected. The selection then puts TRUE in the appropriate row of column A in
the sheet, "SheetNames". The formula in D2 finds the matching sheet number
using the formula =VLOOKUP(TRUE,A356,4,FALSE). All this works fine. What
doesn't seem to work is the line...
Sheets(ss).Select
It does select a sheet, but not the correct sheet as it should, according to
the number shown in D2.

For instance, if I select, "NSW-MSE-F" in the userform, it will show the
number 36 in D2 in the sheet, "SheetNames, but sheet "SA-MSE-F" is selected
instead, which is sheet 18.

I've changed the order of the sheets to conform with the order they appear
in the userform list, but that has now made the situation worse.

Does it mean that I need to renumber every sheet so it corresponds with it's
tab position for this thing to work? If so, is there a macro that will do
that for me quickly?

Private Sub OKButton_Click()
On Error GoTo Endit
Application.Calculation = xlCalculationManual
Dim ss As Variant
Set ss = Sheets("SheetNames").Range("D2")

With Sheet60
..Range("a3") = ListBox1.Selected(0)
..Range("a4") = ListBox1.Selected(1)
..Range("a5") = ListBox1.Selected(2)
..Range("a6") = ListBox1.Selected(3)
..Range("a7") = ListBox1.Selected(4)
..Range("a8") = ListBox1.Selected(5)
..Range("a9") = ListBox1.Selected(6)
..Range("a10") = ListBox1.Selected(7)
..Range("a11") = ListBox1.Selected(8)
..Range("a12") = ListBox1.Selected(9)
..Range("a13") = ListBox1.Selected(10)
..Range("a14") = ListBox1.Selected(11)
..Range("a15") = ListBox1.Selected(12)
..Range("a16") = ListBox1.Selected(13)
..Range("a17") = ListBox1.Selected(14)
..Range("a18") = ListBox1.Selected(15)
..Range("a19") = ListBox1.Selected(16)
..Range("a20") = ListBox1.Selected(17)
..Range("a21") = ListBox1.Selected(18)
..Range("a22") = ListBox1.Selected(19)
..Range("a23") = ListBox1.Selected(20)
..Range("a24") = ListBox1.Selected(21)
..Range("a25") = ListBox1.Selected(22)
..Range("a26") = ListBox1.Selected(23)
..Range("a27") = ListBox1.Selected(24)
..Range("a28") = ListBox1.Selected(25)
..Range("a29") = ListBox1.Selected(26)
..Range("a30") = ListBox1.Selected(27)
..Range("a31") = ListBox1.Selected(28)
..Range("a32") = ListBox1.Selected(29)
..Range("a33") = ListBox1.Selected(30)
..Range("a34") = ListBox1.Selected(31)
..Range("a35") = ListBox1.Selected(32)
..Range("a36") = ListBox1.Selected(33)
..Range("a37") = ListBox1.Selected(34)
..Range("a38") = ListBox1.Selected(35)
..Range("a39") = ListBox1.Selected(36)
..Range("a40") = ListBox1.Selected(37)
..Range("a41") = ListBox1.Selected(38)
..Range("a42") = ListBox1.Selected(39)
..Range("a43") = ListBox1.Selected(40)
..Range("a44") = ListBox1.Selected(41)
..Range("a45") = ListBox1.Selected(42)
..Range("a46") = ListBox1.Selected(43)
..Range("a47") = ListBox1.Selected(44)
..Range("a48") = ListBox1.Selected(45)
..Range("a49") = ListBox1.Selected(46)
..Range("a50") = ListBox1.Selected(47)
..Range("a51") = ListBox1.Selected(48)
..Range("a52") = ListBox1.Selected(49)
..Range("a53") = ListBox1.Selected(50)
..Range("a54") = ListBox1.Selected(51)
..Range("a55") = ListBox1.Selected(52)
..Range("a56") = ListBox1.Selected(53)
End With
Unload Me
Application.Calculation = xlCalculationAutomatic
Sheets(ss).Select
Endit:
End Sub

Private Sub UserForm_Initialize()
With ufSelectSheet.ListBox1
.RowSource = ""
.AddItem "ACT-MSE-F"
.AddItem "ACT-MSE-U"
.AddItem "ACT-G-F"
.AddItem "ACT-G-UF"
.AddItem "ACT-R"
.AddItem "ACT-L"

.AddItem "ANO-MSE-F"
.AddItem "ANO-MSE-U"
.AddItem "ANO-G-F"
.AddItem "ANO-G-UF"
.AddItem "ANO-R"
.AddItem "ANO-L"

.AddItem "NSW-MSE-F"
.AddItem "NSW-MSE-U"
.AddItem "NSW-G-F"
.AddItem "NSW-G-UF"
.AddItem "NSW-R"
.AddItem "NSW-L"

.AddItem "NT-MSE-F"
.AddItem "NT-MSE-U"
.AddItem "NT-G-F"
.AddItem "NT-G-UF"
.AddItem "NT-R"
.AddItem "NT-L"

.AddItem "QLD-MSE-F"
.AddItem "QLD-MSE-U"
.AddItem "QLD-G-F"
.AddItem "QLD-G-UF"
.AddItem "QLD-R"
.AddItem "QLD-L"

.AddItem "SA-MSE-F"
.AddItem "SA-MSE-U"
.AddItem "SA-G-F"
.AddItem "SA-G-UF"
.AddItem "SA-R"
.AddItem "SA-L"

.AddItem "TAS-MSE-F"
.AddItem "TAS-MSE-U"
.AddItem "TAS-G-F"
.AddItem "TAS-G-UF"
.AddItem "TAS-R"
.AddItem "TAS-L"

.AddItem "VIC-MSE-F"
.AddItem "VIC-MSE-U"
.AddItem "VIC-G-F"
.AddItem "VIC-G-UF"
.AddItem "VIC-R"
.AddItem "VIC-L"

.AddItem "WA-MSE-F"
.AddItem "WA-MSE-U"
.AddItem "WA-G-F"
.AddItem "WA-G-UF"
.AddItem "WA-R"
.AddItem "WA-L"

.MultiSelect = fmMultiSelectSingle
End With
End Sub

Thanks for any help!!
Rob