Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Selecting a Worksheet with VBA
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting a row in a worksheet | Excel Worksheet Functions | |||
Selecting Next Worksheet | Excel Programming | |||
selecting a worksheet | Excel Worksheet Functions | |||
selecting cell range in other worksheet without switching to worksheet | Excel Programming | |||
Selecting Last Worksheet | Excel Programming |