View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
MSweetG222 MSweetG222 is offline
external usenet poster
 
Posts: 158
Default copy the valued of named ranges

Steve,

Here is a way of doing this without a lot of code...

1. In Book1...go to Insert | Name | Paste... | Paste List
That will give you all the named ranges in Book1

2. In the column next to the named range enter the following formula:
=INDIRECT("Book2!"&A1) <<< where A1 = 1st Named Range in Book1

This is an indirect reference to the named range in cell A1 as it exists in
Book2. This value will only be correct when Book2 is open.

3. In Book1, press Alt+F11 to enter VBE
go to Insert | Module
In the new module, enter the following code:

Sub Auto_Open()
ActiveWB = ActiveWorkbook.Name
Workbooks.Open ("C:\mytest.xls")
Workbooks(ActiveWB).Activate
End Sub

Note: this does not acct for your macro security settings. If your macro
security is set to HIGH, then you will need to "sign" your VBA code. It is
very easy to create you own trust certificate.

Good Luck!!
--
Thx
MSweetG222



"steve" wrote:

There are 2 workbooks. I want Book1 to open Book2. I want to loop through
all of the named ranges in Book1!Sheet1. Everytime that named range also
exists in Book2!Sheet2, I want to copy the value of that named range on
Book2!Sheet2 into Book1!Sheet1.

am i making any sense?

thanks in advance!