PDA

View Full Version : How to copy named ranges to another workbook



davidw
11-22-2008, 05:51 PM
I have 2 workbooks. 1 is the database. 2 is the macro driven program. I want to copy the named ranges from #1 to #2 file. At present I am copying the data but the named ranges wont copy from file1 to file2. Any help would be appreciated.

GTO
11-22-2008, 06:02 PM
I have 2 workbooks. 1 is the database. 2 is the macro driven program. I want to copy the named ranges from #1 to #2 file. At present I am copying the data but the named ranges wont copy from file1 to file2. Any help would be appreciated.

Greetings David,

I'm afraid I don't understand. What is the difference between "the data" and "the named ranges"?

Are you trying to:

a) Take the definition, such as "A1:H20" and store this info?

or

b) If "A1:H20" is named "MyRange" for instance, then do you want to also name this range "myRange" in the coded workbook?

Hope to help,

Mark

davidw
11-22-2008, 06:06 PM
choice B is closest. I have about 30 ranges with names.
A1:B5 is named "myrange" I am copying the data but the names dont copy over. I have macros that call them up. The macros wont run because they cant find the names.

GTO
11-22-2008, 06:22 PM
With sample data (no private/company info), could you zip two workbooks and attach? I think it would be much easier to see what's going on, ie - are the ranges on different sheets, are we taking them to the same location(s) in the codified workbook, etc. (Not to mention of course, this saves any "helper" or "answerer" from ea having to build duplicates.

Thanks,

Mark

davidw
11-22-2008, 06:33 PM
Ill have to get with you tomorrow because the file is at work. Sorry. Catch you later. Thanks

GTO
11-22-2008, 07:45 PM
No problem whatsoever. I'm heading out in a bit myself, but as I started bashing away at my poor ol' laptop already, maybe just this much would help me 'get my head around it' a bit, in case I am up late...

Let's say SourceWB.xls has named ranges:

"MyRange", which is on Sheet1, and = "A1:H20"
"MySecondRange", which is also on Sheet1, and = "M6:N2000"

"MyOtherRange", which is on Sheet2, and = "C10:E4500"
"MyLastRange", which is on Sheet2, and = "M6:N2000" (so on another sheet, but address is same as second).

Anyways, if this helps you describe the parameters better, great, if not, and better to wait til you can attach samples, that's fine and or probably a better idea anyways. I just don't know how tomorrow looks, and thought I might be dinking around later tonight.

Have a safe/fun Saturday night,

Mark

mikerickson
11-22-2008, 11:01 PM
Dim oneName As Name
For Each oneName In Workbooks("Workbook1").Names
With oneName.RefersToRange
With Workbooks("workbook2").Sheets(.Parent.Name).Range(.Address)
.Value = oneName.RefersToRange.Value
.Name = .Name
End With
End With
Next oneName