Help! Selecting a Worksheet with VBA
 

Hello,

Ok we all know that I can select a sheet on Excel by doing:
sheets("Name").select or worksheets("Name").select

But my problem is that I take the name from a cell with something like:

Dim Name as String
Name = Range("A1").value

If I now do:
sheet(Name).select

I get an error (Name as to be an Object, not a string). I can I do
this? It shouldn't be that hard to select a sheet from a cell value!

Damm VBA!

Regards,

Yona

Help! Selecting a Worksheet with VBA
 

This is working OK

Add also the sheet name to the name string
sheet(Name).select
you forgot the s


Dim Name As String
Name = Sheets("Sheet1").Range("A1").Value

Sheets(Name).Select


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


wrote in message oups.com...
Hello,

Ok we all know that I can select a sheet on Excel by doing:
sheets("Name").select or worksheets("Name").select

But my problem is that I take the name from a cell with something like:

Dim Name as String
Name = Range("A1").value

If I now do:
sheet(Name).select

I get an error (Name as to be an Object, not a string). I can I do
this? It shouldn't be that hard to select a sheet from a cell value!

Damm VBA!

Regards,

Yona

Help! Selecting a Worksheet with VBA
 

Yona,
1. Don't use "Name" as a variable, it is reserved for use by Excel.
2. Your code is missing a character...
sheet(Name).select
Should be...
Sheets(Name).Select
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




wrote in message
Hello,
Ok we all know that I can select a sheet on Excel by doing:
sheets("Name").select or worksheets("Name").select
But my problem is that I take the name from a cell with something like:

Dim Name as String
Name = Range("A1").value

If I now do:
sheet(Name).select
I get an error (Name as to be an Object, not a string). I can I do
this? It shouldn't be that hard to select a sheet from a cell value!
Damm VBA!
Regards,
Yona

Help! Selecting a Worksheet with VBA
 

Yona

This works for me........xl2003

Dim Name As String
Name = Range("A1").Value
Sheets(Name).Select

Note the Sheets as opposed to Sheet


Gord Dibben MS Excel MVP


On 18 Dec 2006 11:09:15 -0800, wrote:

Hello,

Ok we all know that I can select a sheet on Excel by doing:
sheets("Name").select or worksheets("Name").select

But my problem is that I take the name from a cell with something like:

Dim Name as String
Name = Range("A1").value

If I now do:
sheet(Name).select

I get an error (Name as to be an Object, not a string). I can I do
this? It shouldn't be that hard to select a sheet from a cell value!

Damm VBA!

Regards,

Yona

Help! Selecting a Worksheet with VBA
 

wrote:
Hello,

Ok we all know that I can select a sheet on Excel by doing:
sheets("Name").select or worksheets("Name").select

But my problem is that I take the name from a cell with something like:

Dim Name as String
Name = Range("A1").value

If I now do:
sheet(Name).select

I get an error (Name as to be an Object, not a string). I can I do
this? It shouldn't be that hard to select a sheet from a cell value!

Damm VBA!

Regards,

Yona

Help! Selecting a Worksheet with VBA
 

Thanks all for your answers,

If I create a new book, put "Sheet2" on A1 and add your code I get the
following:

Run time error 9
Subscript out of range

I don't know why this works on your excel and not mine (XL 2003 too!).

Regards,

Yona


Gord Dibben wrote:
Yona

This works for me........xl2003

Dim Name As String
Name = Range("A1").Value
Sheets(Name).Select

Note the Sheets as opposed to Sheet


Gord Dibben MS Excel MVP


On 18 Dec 2006 11:09:15 -0800, wrote:

Hello,

Ok we all know that I can select a sheet on Excel by doing:
sheets("Name").select or worksheets("Name").select

But my problem is that I take the name from a cell with something like:

Dim Name as String
Name = Range("A1").value

If I now do:
sheet(Name).select

I get an error (Name as to be an Object, not a string). I can I do
this? It shouldn't be that hard to select a sheet from a cell value!

Damm VBA!

Regards,

Yona

Help! Selecting a Worksheet with VBA
 

Thank you for the answer,

Unfortunately this doesn't work for me. I get the following error:

run time error 9
Subscript out of range

Any idea??

Regards,

Yona


Jim Thomlinson wrote:
This will work so long as the value in the cell is actually the name of one
of the sheets.

Sheets(Range("A1").Value).Select

I would be inclined to confirm that the sheet exists before I try to select
it...

if SheetExists(Range("A1").Value) then Sheets(Range("A1").Value).Select

...

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function

--
HTH...

Jim Thomlinson


" wrote:

Hello,

Ok we all know that I can select a sheet on Excel by doing:
sheets("Name").select or worksheets("Name").select

But my problem is that I take the name from a cell with something like:

Dim Name as String
Name = Range("A1").value

If I now do:
sheet(Name).select

I get an error (Name as to be an Object, not a string). I can I do
this? It shouldn't be that hard to select a sheet from a cell value!

Damm VBA!

Regards,

Yona

Help! Selecting a Worksheet with VBA
 

That's because you didn't declare from which sheet range("A1) you took the
data from,so the correct answer would be;

'In a new workbook
Private Sub Test()
Dim Name As String
Name = Worksheets("sheet1").Range("A1").Value
Sheets(Name).Select
End Sub

wrote in message
ups.com...
Thanks all for your answers,

If I create a new book, put "Sheet2" on A1 and add your code I get the
following:

Run time error 9
Subscript out of range

I don't know why this works on your excel and not mine (XL 2003 too!).

Regards,

Yona


Gord Dibben wrote:
Yona

This works for me........xl2003

Dim Name As String
Name = Range("A1").Value
Sheets(Name).Select

Note the Sheets as opposed to Sheet


Gord Dibben MS Excel MVP


On 18 Dec 2006 11:09:15 -0800, wrote:

Hello,

Ok we all know that I can select a sheet on Excel by doing:
sheets("Name").select or worksheets("Name").select

But my problem is that I take the name from a cell with something like:

Dim Name as String
Name = Range("A1").value

If I now do:
sheet(Name).select

I get an error (Name as to be an Object, not a string). I can I do
this? It shouldn't be that hard to select a sheet from a cell value!

Damm VBA!

Regards,

Yona