Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,814
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.programming
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!

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,814
Default 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!

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 158
Default 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!

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,814
Default 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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
trying to copy a worksheet containing named ranges to anotherworksheet cil9mxm Excel Worksheet Functions 1 December 3rd 08 07:06 PM
Copy worksheet with named ranges to new workbook and keep names Sandy Excel Worksheet Functions 0 July 11th 08 04:37 PM
How do I copy a group of worksheets with named ranges in Excel 200 sc Excel Worksheet Functions 2 September 26th 06 12:16 AM
copy all named ranges in a sheet to seperate sheets Chris Salcedo Excel Programming 8 October 10th 05 06:23 AM
Like 123, allow named ranges, and print named ranges WP Excel Discussion (Misc queries) 1 April 8th 05 06:07 PM


All times are GMT +1. The time now is 11:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"