copy all named ranges in a sheet to seperate sheets
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
|