defined names

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200
How can i copy the defined names from one workbook and paste the same into another workbook.Regards
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Mark W.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
Enter a reference to the defined name in a cell, copy it, and paste into 2nd workbook.
 

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200
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.

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,654
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

Board Regular
Joined
Sep 17, 2002
Messages
200

ADVERTISEMENT

=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,
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

ehsas

Board Regular
Joined
Sep 17, 2002
Messages
200
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

Legend
Joined
Feb 17, 2002
Messages
9,700
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

Board Regular
Joined
Sep 17, 2002
Messages
200
myNme = Range(nm).Address(False, False)

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

Regards,
 

Forum statistics

Threads
1,143,709
Messages
5,720,390
Members
422,282
Latest member
psunith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top