[SOLVED] copy all named ranges in a sheet to seperate sheets

[Chris Salcedo]

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

[Rowan Drummond]

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
>

[Gary Keramidas]

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" <csalcedo@gmail.com> wrote in message
news:1128914592.544753.266010@g44g2000cwa.googlegroups.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
>

[DGolds]

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

[Chris Salcedo]

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

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

You da Wiz......

Thanks

[Chris Salcedo]

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

[Chris Salcedo]

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

Chris

[Gary Keramidas]

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" <csalcedo@gmail.com> wrote in message
news:1128917678.687057.293510@g14g2000cwa.googlegroups.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
>

[Rowan Drummond]

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
>