defined names

[ehsas]

How can i copy the defined names from one workbook and paste the same into another workbook.Regards

 

[Mark W.]

Enter a reference to the defined name in a cell, copy it, and paste into 2nd workbook.

 

[ehsas]

Could you explain how to do this.Becaus I am using insert-name-paste and clinking past the list in the worksheet but I couldn't able to define the same in the other workbook.Please if you could help.Regards,.

 

[Mark W.]

Suppose you have a defined name, Range, that refers to Sheet1!$A$1:$A$2... Enter =Range in a cell. Copy that cell, and paste into 2nd workbook. Now the 2nd workbook has the defined name, Range, referring to A1:A2 on it's own Sheet1.

 

[ehsas]

=OFFSET(sheet1!$A$11,COUNTA(sheet1!$A:$A)-sheet1!$E$1,0,sheet1!$E$1,1)

I have a bove formul which is defined as xvalues in workbook1.I want to paste the same xvalues in workbook2.
I couldn't understand the way you said could explain let more.Regards,

 

[ehsas]

any help.Regards,

 

[Andrew Poulsom]

Do you mean you want to define a name in Workbook2 that refers to your xvalues range in Workbook1?

 

[ehsas]

No.I wan to create similar defined names in workbook2 for which I have to refer to workbook1 copy each defined names formula and then paste in workbook2.Which is time consuming.I want in one time all to be copied in workbook2.regards,

 

[NateO]

Not sure I'm following this post, but here's some code to bring the whole group of names accross with formulae and values... (hope the number of sheets line up):<pre>
Sub Cpy_NmRngs()
Dim nm As Name, myNme1 As String, myNme2 As String, sht As Integer
Dim NewBook As String
NewBook = "Book36" 'change Book36 to the name of target workbook
For Each nm In ThisWorkbook.Names
myNme1 = Range(nm).Address(False, False)
sht = Range(nm).Worksheet.Index
myNme2 = nm.Name
Workbooks(NewBook).Sheets(sht).Range(myNme1).Name = myNme2
If Range(nm).HasFormula Then
Workbooks(NewBook).Sheets(sht).Range(myNme1).Formula = _
Range(nm).Formula
Else: Workbooks(NewBook).Sheets(sht).Range(myNme1) = Range(nm)
End If
Next nm
End Sub</pre><pre></pre>
Edit: Fixed bunk vairable, sorry...
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by NateO on 2002-10-23 17:00

 

[ehsas]

myNme = Range(nm).Address(False, False)

let me say that the macro stops at above statement.Why?

Regards,