| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using cell as worksheet reference (macro)
Hi,
I would like to use the content of a cell as a worksheet reference. When the macro is run, the name in the cell should open the worksheet of the same name in another workbook. So, in workbook A, I have in cell A1 "ed" (without the quotes). I want to activate worksheet "ed" in workbook B. Workbook B is already open. I have the following code in Workbook A, but is not working: Windows("B.xls").Worksheets(Range("A1").Value).Act ivate Any suggestions? Ed |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using cell as worksheet reference (macro)
Break it out as multiple lines:
xSheet = Range("A1") Windows("Book1").Activate Worksheets(xSheet).Select Alternative, if you want to save a line: Windows("Book1").Activate Worksheets(ThisWorkbook.Sheets("My Sheet").Range("A1").Value).Select -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Edwin" wrote: Hi, I would like to use the content of a cell as a worksheet reference. When the macro is run, the name in the cell should open the worksheet of the same name in another workbook. So, in workbook A, I have in cell A1 "ed" (without the quotes). I want to activate worksheet "ed" in workbook B. Workbook B is already open. I have the following code in Workbook A, but is not working: Windows("B.xls").Worksheets(Range("A1").Value).Act ivate Any suggestions? Ed |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Using cell as worksheet reference (macro)
Thanks Luke, works great!
Edwin "Luke M" wrote: Break it out as multiple lines: xSheet = Range("A1") Windows("Book1").Activate Worksheets(xSheet).Select Alternative, if you want to save a line: Windows("Book1").Activate Worksheets(ThisWorkbook.Sheets("My Sheet").Range("A1").Value).Select -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Edwin" wrote: Hi, I would like to use the content of a cell as a worksheet reference. When the macro is run, the name in the cell should open the worksheet of the same name in another workbook. So, in workbook A, I have in cell A1 "ed" (without the quotes). I want to activate worksheet "ed" in workbook B. Workbook B is already open. I have the following code in Workbook A, but is not working: Windows("B.xls").Worksheets(Range("A1").Value).Act ivate Any suggestions? Ed |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you get a macro to reference the worksheet it is being run | Excel Discussion (Misc queries) | |||
| Cell Reference Macro | Excel Discussion (Misc queries) | |||
| cell reference in macro | Excel Discussion (Misc queries) | |||
| Macro to Create New Worksheet and Reference Cell in Old Worksheet As Tab Name - "Object Required" Error | Excel Discussion (Misc queries) | |||
| Worksheet reference (i.e placing worksheet name in a cell) | Excel Worksheet Functions | |||
Linear Mode
