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