Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
Hi,
I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
This means AString is name of the work book and
not name of the sheet. Try AString = "Input" Sheets(AString).Select '= Will work Sharad "Hans Weustink" <Hans wrote in message ... Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
What do you have in that cell? What error do you get?
-- HTH RP (remove nothere from the email address if mailing direct) "Hans Weustink" <Hans wrote in message ... Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
The reason
Sheets(AString).Select does not work is because you have assigned to the variable AString the value that is in Range A1 of your Input Sheet If range A1 of your Input sheet contains the name of another Sheet, then your code will select that sheet. "Hans Weustink" wrote: Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
That's indeed the intention of the AString variable, but the value of the
cell is identical to a different sheet then the current one and yet the code doesn't work. So my question merely was what alternative is there are change do I have to make to the code because it doesn't work, Thanks, Hans "gocush" wrote: The reason Sheets(AString).Select does not work is because you have assigned to the variable AString the value that is in Range A1 of your Input Sheet If range A1 of your Input sheet contains the name of another Sheet, then your code will select that sheet. "Hans Weustink" wrote: Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
In the Cell is a (changing) number that corresponds to a sheet with the same
name, however the code gives an error: "subscript out of range" So what do I have to change to the code, because I can't get it to work. Or do you have an exmple when it does work? Thanks, Hans "Bob Phillips" wrote: What do you have in that cell? What error do you get? -- HTH RP (remove nothere from the email address if mailing direct) "Hans Weustink" <Hans wrote in message ... Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
No AString is the value of a specific cell, which I want to us to open a
sheet with the same name. Naming the references is the same as naming the sheet, then the variable aspect is gone. However could something with cell format (text, with " ", without " ") have anything to do with it? Thanks, Hans "Sharad Naik" wrote: This means AString is name of the work book and not name of the sheet. Try AString = "Input" Sheets(AString).Select '= Will work Sharad "Hans Weustink" <Hans wrote in message ... Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
If the workbook and the string have the same name, the workbook should also
include the .xls extension. If the cell has that extension, then the worksheet won't work. You cannot have a variable to reference the book and sheet without some manipulation, such as, if A1 has the value "ABC", then Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString & ".xls").Activate = works Sheets(AString).Select = doesn't work End Sub What is the exact value in A1? -- HTH RP (remove nothere from the email address if mailing direct) "Hans Weustink" wrote in message ... In the Cell is a (changing) number that corresponds to a sheet with the same name, however the code gives an error: "subscript out of range" So what do I have to change to the code, because I can't get it to work. Or do you have an exmple when it does work? Thanks, Hans "Bob Phillips" wrote: What do you have in that cell? What error do you get? -- HTH RP (remove nothere from the email address if mailing direct) "Hans Weustink" <Hans wrote in message ... Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
You can use Range("A1").Text
Instead of .Value sharad "Hans Weustink" wrote in message ... No AString is the value of a specific cell, which I want to us to open a sheet with the same name. Naming the references is the same as naming the sheet, then the variable aspect is gone. However could something with cell format (text, with " ", without " ") have anything to do with it? Thanks, Hans "Sharad Naik" wrote: This means AString is name of the work book and not name of the sheet. Try AString = "Input" Sheets(AString).Select '= Will work Sharad "Hans Weustink" <Hans wrote in message ... Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
Never Mind, I got it working now I changed the formula in the cell from
=(cell) to =concatenate(cell) so it is a text value instead of a numeric value. Thanks anyway, Hans "gocush" wrote: The reason Sheets(AString).Select does not work is because you have assigned to the variable AString the value that is in Range A1 of your Input Sheet If range A1 of your Input sheet contains the name of another Sheet, then your code will select that sheet. "Hans Weustink" wrote: Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
Never Mind, I got it working now I changed the formula in the cell from
=(cell) to =concatenate(cell) so it is a text value instead of a numeric value. Thanks anyway, Hans "Sharad Naik" wrote: This means AString is name of the work book and not name of the sheet. Try AString = "Input" Sheets(AString).Select '= Will work Sharad "Hans Weustink" <Hans wrote in message ... Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select sheet based on reference
Never Mind, I got it working now I changed the formula in the cell from
=(cell) to =concatenate(cell) so it is a text value instead of a numeric value. Thanks anyway, Hans "Bob Phillips" wrote: What do you have in that cell? What error do you get? -- HTH RP (remove nothere from the email address if mailing direct) "Hans Weustink" <Hans wrote in message ... Hi, I'm able to selecet a workbook, but not a sheet based on a reference like so: Sub test() Dim AString AString = Sheets("Input").Range("A1").Value Windows(AString).Activate = works Sheets(AString).Select = doesn't work End Sub Can someone think of a (alternative) way it will work? Thanks in advance, Hans Weustink |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using VBA select sll sheets based on Criteria on each sheet. | Excel Discussion (Misc queries) | |||
Copy rows to new sheet based cell reference, not value | Excel Discussion (Misc queries) | |||
How do I select price from sheet.b where sheet.a part no = sheet.b | Excel Worksheet Functions | |||
Select Sheet then Select Range | Excel Programming | |||
Select sheet based on cell value | Excel Programming |