Switch to different sheet using cell value
 

I have searched and searched for this answer but everything I have
found and tried did not work.

The macro I use is very simple. It creats and names a second
worksheet that uses different formulas to return values based on the
data in the first worksheet. I also populate Cell B2 in the new sheet
with the name of the original worksheet.

After the second worksheet is created I want the macro to go back to
the original and make changes -add a column, delete a column, resort,
etc. The name of the first worksheet changes daily.

This code works fine for what I need and so I use it:

Worksheets(1).Activate

But what I want to know how to do, for my own understanding, is how to
Activate a worksheet based on a Cell value. I have tried Indirect and
a number of other things with no luck.

Worksheets(B2).Activate does not work because it is looking for a
worksheet named B2.

Is there an easy way to say Worksheets(X).Activate where "X" is the
text in a Cell like B2?

Thanks,

Dow

Switch to different sheet using cell value
 

Look at this:

Worksheets(Range("B2").Value).Activate

or

X=Range("B2").Value
Worksheets(X).Activate

Hopes this helps.

---
Per

"Dow" skrev i meddelelsen
...
I have searched and searched for this answer but everything I have
found and tried did not work.

The macro I use is very simple. It creats and names a second
worksheet that uses different formulas to return values based on the
data in the first worksheet. I also populate Cell B2 in the new sheet
with the name of the original worksheet.

After the second worksheet is created I want the macro to go back to
the original and make changes -add a column, delete a column, resort,
etc. The name of the first worksheet changes daily.

This code works fine for what I need and so I use it:

Worksheets(1).Activate

But what I want to know how to do, for my own understanding, is how to
Activate a worksheet based on a Cell value. I have tried Indirect and
a number of other things with no luck.

Worksheets(B2).Activate does not work because it is looking for a
worksheet named B2.

Is there an easy way to say Worksheets(X).Activate where "X" is the
text in a Cell like B2?

Thanks,

Dow

Switch to different sheet using cell value
 

Hi
In your macro you should capture the B2 value required e.g.
Dim MySheetName as String
mySheetName = Worksheets("ShheetWithB2").Range("B2").Value

then you can use that later with

Worksheets(mySheetName).Activate

regards
Paul

On Feb 19, 6:23*pm, Dow wrote:
I have searched and searched for this answer but everything I have
found and tried did not work.

The macro I use is very simple. *It creats and names a second
worksheet that uses different formulas to return values based on the
data in the first worksheet. *I also populate Cell B2 in the new sheet
with the name of the original worksheet.

After the second worksheet is created I want the macro to go back to
the original and make changes -add a column, delete a column, resort,
etc. The name of the first worksheet changes daily.

This code works fine for what I need and so I use it:

Worksheets(1).Activate

But what I want to know how to do, for my own understanding, is how to
Activate a worksheet based on a Cell value. *I have tried Indirect and
a number of other things with no luck.

Worksheets(B2).Activate does not work because it is looking for a
worksheet named B2.

Is there an easy way to say Worksheets(X).Activate where "X" is the
text in a Cell like B2?

Thanks,

Dow

Switch to different sheet using cell value
 

I could have sworn that I had tried this already.

Works perfectly.

Thank you, Per.

Dow.

On Feb 19, 11:46*am, "Per Jessen" wrote:
Look at this:

Worksheets(Range("B2").Value).Activate

or

X=Range("B2").Value
Worksheets(X).Activate

Hopes this helps.

---
Per

Switch to different sheet using cell value
 

I was trying to figure out how to make the "Dim" option work. I knew
there was a way.

Thanks for your help also, Paul.

Dow.

On Feb 19, 12:03*pm, wrote:
Hi
In your macro you should capture the B2 value required *e.g.
Dim MySheetName as String
mySheetName = Worksheets("ShheetWithB2").Range("B2").Value

then you can use that later with

Worksheets(mySheetName).Activate

regards
Paul