[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Help! Selecting a Worksheet with VBA

yonathan.sabbah

12/18/2006 7:09:00 PM

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

Jim Cone

12/18/2006 7:26:00 PM

0

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



<yonathan.sabbah@gmail.com>
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

Ron de Bruin

12/18/2006 7:26:00 PM

0

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


<yonathan.sabbah@gmail.com> wrote in message news:1166468955.770857.253600@l12g2000cwl.googlegroups.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
>

Gord Dibben

12/18/2006 7:46:00 PM

0

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, yonathan.sabbah@gmail.com 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

yonathan.sabbah

12/18/2006 7:48:00 PM

0


yonathan.sabbah@gmail.com 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

yonathan.sabbah

12/18/2006 7:51:00 PM

0

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, yonathan.sabbah@gmail.com 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

yonathan.sabbah

12/18/2006 8:09:00 PM

0

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
>
>
> "yonathan.sabbah@gmail.com" 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
> >
> >

iKKi

12/18/2006 8:53:00 PM

0

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

<yonathan.sabbah@gmail.com> wrote in message
news:1166471482.421353.121990@73g2000cwn.googlegroups.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, yonathan.sabbah@gmail.com 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
>