if for some reason you wanted to make a backup of the ranges, or maybe duplicate
ranges from one workbook to another, you could use this. then just take the
output of the immediate window, paste it in a sub and run it to re-create the
ranges. sheet names would obviously have to be identical.
Sub BackupRanges()
Dim nm As Name
sName = ActiveSheet.Name
For Each nm In ThisWorkbook.Names
Debug.Print "ActiveWorkbook.Names.Add Name:=" & """" & nm.Name & """" & _
", Refersto:=""" & nm
Next
End Sub
--
Gary
"Sean" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks Jon, its just to remove the names and leave everthing else
> intact. I'll get it a go
>
>
> Jon Peltier wrote:
>
>> To remove the names, but leave the data intact:
>>
>> For Each nName In ActiveWorkbook.Names
>> nName.Delete
>> Next
>>
>> To clear the ranges of data but leave the names:
>>
>> For Each nName In ActiveWorkbook.Names
>> On Error Resume Next
>> nName.RefersToRange.ClearContents
>> On Error Goto 0
>> Next
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Tutorials and Custom Solutions
>> http://PeltierTech.com
>> _______
>>
>>
>> "Sean" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > Would anyone have some code that I could run to delete all named ranges
>> > on all woksheets within a file?
>> >
>> >
>> > Thanks
>> >
>
Similar Threads
