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

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

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