Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trying to copy a worksheet containing named ranges to anotherworksheet | Excel Worksheet Functions | |||
Copy worksheet with named ranges to new workbook and keep names | Excel Worksheet Functions | |||
How do I copy a group of worksheets with named ranges in Excel 200 | Excel Worksheet Functions | |||
copy all named ranges in a sheet to seperate sheets | Excel Programming | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) |