copy the valued of named ranges
 

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!

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!

copy the valued of named ranges
 

Thanks for the Response!

but is there a way i can contain this just in my code? below i explain how
i thought the code would flow. I'm just not sure how to code it correctly.

this code woudl be triggered by the user pressing a button:

For Each name in thisworkbook.worksheets("test").names
if this name also exists on book2.worksheets("test") Then
pass the value of the name in book2 to the value of name in
thisworkbook
else
'do nothing...(skip this name)
end if
next name



"MSweetG222" wrote:

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!

copy the valued of named ranges
 

Steve -

Here is some sample code to get you going:

On Error Resume Next

For Each Name In ThisWorkbook.Sheets("Test").Names
WB1Name = Name.Name
WB2Name = Workbooks("Book2.xls").Sheets("Test").Names(WB1Nam e).Name

If WB1Name = WB2Name Then
ActiveCell.Value = WB2Name
ActiveCell.Offset(0, 1).Value =
Workbooks("Book2.xls").Sheets("Test").Range(WB2Nam e).Value
ActiveCell.Offset(1, 0).Select
End If

Next Name


On Error Goto HandleErr '<<< puts code back to your error handler (where
HandleErr is the name of your error handler)


--
Thx
MSweetG222



"steve" wrote:

Thanks for the Response!

but is there a way i can contain this just in my code? below i explain how
i thought the code would flow. I'm just not sure how to code it correctly.

this code woudl be triggered by the user pressing a button:

For Each name in thisworkbook.worksheets("test").names
if this name also exists on book2.worksheets("test") Then
pass the value of the name in book2 to the value of name in
thisworkbook
else
'do nothing...(skip this name)
end if
next name



"MSweetG222" wrote:

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!

copy the valued of named ranges
 

Thanks!

I'll be working that into my code tomorrow. I'll let you knwo how it goes

"MSweetG222" wrote:

Steve -

Here is some sample code to get you going:

On Error Resume Next

For Each Name In ThisWorkbook.Sheets("Test").Names
WB1Name = Name.Name
WB2Name = Workbooks("Book2.xls").Sheets("Test").Names(WB1Nam e).Name

If WB1Name = WB2Name Then
ActiveCell.Value = WB2Name
ActiveCell.Offset(0, 1).Value =
Workbooks("Book2.xls").Sheets("Test").Range(WB2Nam e).Value
ActiveCell.Offset(1, 0).Select
End If

Next Name


On Error Goto HandleErr '<<< puts code back to your error handler (where
HandleErr is the name of your error handler)


--
Thx
MSweetG222



"steve" wrote:

Thanks for the Response!

but is there a way i can contain this just in my code? below i explain how
i thought the code would flow. I'm just not sure how to code it correctly.

this code woudl be triggered by the user pressing a button:

For Each name in thisworkbook.worksheets("test").names
if this name also exists on book2.worksheets("test") Then
pass the value of the name in book2 to the value of name in
thisworkbook
else
'do nothing...(skip this name)
end if
next name



"MSweetG222" wrote:

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!