Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Switch lookup function to another sheet | Excel Worksheet Functions | |||
Calendar Control - Switch to Sheet X | Excel Programming | |||
Excel: Arrows scroll sheet instead of move cursor. How to switch | Excel Discussion (Misc queries) | |||
Can I switch back to Excel sheet while a form is shown? | Excel Programming | |||
Trying to switch to a different sheet in a macro? | Excel Programming |