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

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

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

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

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

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

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

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

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

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

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

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