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

Try something like:

Sub copythem()
Dim nm As Name
Dim tSht As Worksheet
Dim nSht As Worksheet
Set tSht = Sheets("Sheet1") '<< the sheet with the names
For Each nm In ThisWorkbook.Names
If nm.RefersToRange.Parent.Name = tSht.Name Then
Set nSht = Sheets.Add
Range(nm).Copy nSht.Cells(1)
On Error Resume Next
nSht.Name = nm.Name
On Error GoTo 0
Set nSht = Nothing
End If
Next nm
End Sub


Hope this helps
Rowan

Chris Salcedo wrote:
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

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

This will copy each named range in the workbook to its own new sheet and name
the sheet after the name of the range:

Sub CopyNames()
Dim nm As Name
Dim mySheet As Worksheet
For Each nm In ActiveWorkbook.Names
Range(nm).Copy
Set mySheet = Worksheets.Add(After:=Sheets(Sheets.Count))
mySheet.Name = nm.Name
mySheet.Paste Destination:=Range("A1")
Next nm
Application.CutCopyMode = False
End Sub

HTH,
Dave

"Chris Salcedo" wrote:

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

WOW !!!! this is great works fantastic.....

In the words of the scarecrow from OZ "If I only had a brain"...

You da Wiz......

Thanks

Thanks Guys for all the help I have it working now and am adding some
bells and wistles to the code....

Thanks
To all

Chris

All This stuff is great... Thanks for the help everyone...

Chris

sorry, for some reason i thought you wanted to sum all of the ranges. don't
know what i was thinking<g

--


Gary


"Chris Salcedo" wrote in message
oups.com...
Thanks Guys for all the help I have it working now and am adding some
bells and wistles to the code....

Thanks
To all

Chris

You're welcome.

Chris Salcedo wrote:
WOW !!!! this is great works fantastic.....

In the words of the scarecrow from OZ "If I only had a brain"...

You da Wiz......

Thanks