copy all named ranges in a sheet to seperate sheets

  • Thread starter Thread starter Chris Salcedo
  • Start date Start date
C

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
 
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
 
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
 
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
 
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
 
sorry, for some reason i thought you wanted to sum all of the ranges. don't
know what i was thinking<g>
 
Back
Top