Defined Names

A

axg275

Active Member
Joined
Oct 5, 2004
Messages
308
Hello,

I have a sheet with whole bunch of cells as defined names. I want to make a copy of this sheet and put it into another workbook. I was thinking that I need to un-define all the names and then make a copy. I dont know how to do this besides going manual on every name. There are more than 100 names that are defined and is not practical for me to do.

Do you guys know how to go about this?

Thx
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Thx Lenze,

But how do I deal with the cells that reference these named cells?

Thanks again!
 
Upvote 0
Hope next code could help.
Here some explanation how to use it.
<TABLE style="WIDTH: 253pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=336 border=0 x:str><COLGROUP><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6217" width=170><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6070" width=166><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 128pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=170 height=17>Macro</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 125pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=166></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>List_Cells_Name</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">To list cells' name of the working file in column A</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" height=21>Remove_Cell_Name</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">To remove cells'name when name ARE in column B</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>How to work</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17>Present file already loaded in EXCEL</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17>Load in EXCEL the file where to modify the cells' name: Working file</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=17 x:str="Create a sheet where to store temporary data: Working sheet ">Create a sheet where to store temporary data: Working sheet </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Go to this new sheet</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=21>Launch List_Cells_Name to list cells'name in column A</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=21>In column B copy all cells'name you want to remove: Start at FIRST row</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2 height=21>Launch Remove_Cell_Name to remove cells'name</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Delete the working sheet</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17></TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
Click to expand...
Code: 
Sub List_Cells_Name()
'
Range("A:B").Clear
Range("A1").Select ' Parking Cell
Range("A1").ListNames
Range("A:B").Clear
Range("A1").Select ' Parking Cell
Set nms = ActiveWorkbook.Names
For r = 1 To nms.Count
    Cells(r, 1) = nms(r).Name           '  Cells Name  copied  in column  A
Next
'
End Sub
Sub Remove_Cell_Name()
'
'  Subroutine  to  remove Cells Name
'
I = 1           '  Index in  LIST  cells 'name
J = 1           '  Index in  DELETE  cells 'name
Cell_Name_LIST = Cells(I, 1)
Cell_Name_DELETE = Cells(J, 2)
While Cell_Name_DELETE <> ""
    While (Cell_Name_LIST <> "")
        If (Cell_Name_DELETE = Cell_Name_LIST) Then
        ActiveWorkbook.Names(Cell_Name_DELETE).Delete
        Cells(J, 2) = ""
        End If
        I = I + 1
        Cell_Name_LIST = Cells(I, 1)
    Wend
    J = J + 1
    I = 1
    Cell_Name_LIST = Cells(I, 1)
    Cell_Name_DELETE = Cells(J, 2)
Wend
Beep
End Sub
 
Upvote 0
PCL,

That worked but now my sheet is not working because there are calculations in the sheet that reference to these defined names

ex:

=IF(oil_or_gas="Gas Well",gascf_cfactor,oilcf_cfactor)

What do you think I can do about this?

Thanks a bunch!!
 
Upvote 0
Ok I have whole bunch of cells that were defined as names.

There are whole bunch of calculations that reference these names

ex
=IF(D3="Gas Well",C5,C7)

In my case:

equations reference the name that the cell was named as

so once I remove all the names,.... the calculations are broken because the equations are still using the names that were defined.

so in the example I gave the equation:

=IF(oil_or_gas="Gas Well",gascf_cfactor,oilcf_cfactor)

is looking for cells that were named: oil_or_gas, Gas Well, gascf_cfactor, oilcf_cfactor

now that I deleted them the cells are no longer defined so this equation does not work.

Let me know if this of help explaining this to you.

Thanks
 
Last edited:
Upvote 0
So why did you delete the Names in the 1st place? Hopefully, you have a backup copy of your file.
lenze
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Set filtered column in a table as a defined range
Replies
5
Views
316
SanjayGMusafir
SanjayGMusafir
P
  • Solved
Copy Template Sheet
Replies
6
Views
107
paulsolar
P
R
  • Solved
Macro to Hyperlink Tab to a defined name
Replies
5
Views
561
Revnou
R
R
  • Solved
Highlighting Cells IF Conditions Met
Replies
8
Views
244
awoohaw
awoohaw
M
  • Solved
Find value in workbook written in cell in master sheet and get names of sheets where it was found
2
Replies
17
Views
616
mumps
mumps

Forum statistics

Threads
1,214,812
Messages
6,121,699
Members
449,048
Latest member
81jamesacct

Share this page

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
Back
Top