defined names

[ehsas]

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

 

[ehsas]

could you please try to fix and post the update one.regards,

 

[NateO]

Well, I did, it's back on page 1, you can edit your posts on this site. Here it is again:

<pre>
Option Explicit
Sub Cpy_NmRngs()
Dim nm As Name, myNme1 As String, myNme2 As String, sht As Integer
Dim NewBook As String
''''''Change the Workbook Name Directly Below''''''''''
NewBook = "Book4"
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>

I can get this to run from carsold.xlw in xl2000...

 

[ehsas]

Workbooks(NewBook).Sheets(sht).Range(myNme1).Name = myNme2

I am getting the following error

runtime error 1004 the name is not valid

 

[NateO]

Which name is it tripping on?

Hit debug and hold your cursor over myNme2

_________________
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 18:18

 

[ehsas]

I can see the following:-

mynme2 = "shett1!__123graph_chart 4"

I don't know what that means.Please help.Regards

 

[NateO]

Yes, that's an invalid Excel name for defined ranges, try the following:<pre>
Option Explicit
Sub Cpy_NmRngs()
Dim nm As Name, myNme1 As String, myNme2 As String, sht As Integer
Dim NewBook As String
''''''Change the Workbook Name Directly Below''''''''''
NewBook = "book6"
For Each nm In ThisWorkbook.Names
On Error GoTo 1
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
1: Next nm
End Sub</pre>

_________________
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 19:33

 

[ehsas]

thanks for your help.It copied the defined names.Also by putting resume statement it copies some formula to my book6 worksheet.I don't want to do that.some formula like
=(D7-D8)/D7 i want to red of this.
Regards,

 

[NateO]

It's got to be bringing a formula with a name that isn't working, try the edited code above.

 

[ehsas]

it again stop at below statement

Workbooks(NewBook).Sheets(sht).Range(myNme1).Name = myNme2

with following messege when I check mynme2

mynme2 = "shett1!__123graph_chart 4"

regards,