Select sheet based on reference

[Guest]

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

 

[Sharad Naik]

This means AString is name of the work book and
not name of the sheet.

Try
AString = "Input"
Sheets(AString).Select '=> Will work

Sharad

 

[Bob Phillips]

What do you have in that cell? What error do you get?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Sharad Naik]

You can use Range("A1").Text
Instead of .Value

sharad

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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