adapted from tom olgivy

Dim total As Double
Sub testbsrange()
Dim oname As Object
Dim rng As Range
total = 0
For Each oname In Worksheets("Sheet1").Parent.Names
Set rng = Nothing
On Error Resume Next
Set rng = oname.RefersToRange
On Error GoTo 0
If Not rng Is Nothing Then
total = total + Application.Sum(Range(oname.Name))
MsgBox total
End If
Next oname
End Sub

--


Gary


"Chris Salcedo" wrote in message
oups.com...
I have a sheet that has lots of named ranges. What I need to do is take
each named range and copy it to a new sheet then name the sheet using
the named range. What I dont know how to do is loop through all the
named ranges.

I can do this and it works...

Sub Copy_Range()

ActiveSheet.Range("CPG0162").Select
Selection.Copy
Sheets("Sheet7").Select
Range("A1").Select
ActiveSheet.Paste
End Sub

Thanks