Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
absolute reference to sheet in other workbook
I have a macro that copies specific data from one workbook to another. I need to be able to reference a specific sheet in that other workboo absolutely. The macro checks that the workbook to copy the data into is open tha then stores that workbook in a variable. Set OtherWorkbook = Workbooks("Other Workbook.xls") Now i need to refer to another sheet in the workbook absolutely i order to select it. OtherWorkbook.Sheets(XX) won't work because the index number change when the sheets are moved around, which will be changed by the user. OtherWorkbook.Sheets("Tab Title") won't work because that has a goo possibility of being changed by the user. I'd like to use the Sheet (Name) property in VBA, but syntax like: OtherWorkBook.Sheet12.Select doesn't work either. Thanks -- Brassma ----------------------------------------------------------------------- Brassman's Profile: http://www.excelforum.com/member.php...fo&userid=1329 View this thread: http://www.excelforum.com/showthread.php?threadid=49553 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
absolute reference to sheet in other workbook
The only way I can see doing this is the following. Open your
source workbook ("Other Workbook.xls") in the VBA editor, then go to the Tools menu, choose "VBA Project Properties" and change the name to something unique, e.g., OtherWorkbook. Then open the destination workbook in the editor, go to the Tools menu, choose References, and check the project whose name you just rename, e.g., OtherWorkbook. Then, you can use code like Debug.Print OtherWorkbook.Sheet1.Range("A1").Value Of course, OtherWorkbook must be open while your destination workbook is open. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Brassman" wrote in message ... I have a macro that copies specific data from one workbook to another. I need to be able to reference a specific sheet in that other workbook absolutely. The macro checks that the workbook to copy the data into is open that then stores that workbook in a variable. Set OtherWorkbook = Workbooks("Other Workbook.xls") Now i need to refer to another sheet in the workbook absolutely in order to select it. OtherWorkbook.Sheets(XX) won't work because the index number changes when the sheets are moved around, which will be changed by the user. OtherWorkbook.Sheets("Tab Title") won't work because that has a good possibility of being changed by the user. I'd like to use the Sheet (Name) property in VBA, but syntax like: OtherWorkBook.Sheet12.Select doesn't work either. Thanks. -- Brassman ------------------------------------------------------------------------ Brassman's Profile: http://www.excelforum.com/member.php...o&userid=13290 View this thread: http://www.excelforum.com/showthread...hreadid=495535 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
absolute reference to sheet in other workbook
Although the sheet reference needs to be absolute, the workbook reference is only loosly absolute. The source and destination workbooks change each year, and the macro looks at the workbooks that are currently open in order to determine which ones to use. Thanks though. -- Brassman ------------------------------------------------------------------------ Brassman's Profile: http://www.excelforum.com/member.php...o&userid=13290 View this thread: http://www.excelforum.com/showthread...hreadid=495535 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Absolute Sheet Reference | Excel Worksheet Functions | |||
Absolute cell reference will not remain absolute. | Excel Worksheet Functions | |||
Reference to sheet name in another workbook | Excel Discussion (Misc queries) | |||
Copying a work sheet cell reference as relative not absolute? | Excel Discussion (Misc queries) | |||
See code enclosed - Convert to formulas with absolute reference inculding the sheet references! | Excel Programming |