Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy all named ranges in a sheet to seperate sheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy all named ranges in a sheet to seperate sheets
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy all named ranges in a sheet to seperate sheets
WOW !!!! this is great works fantastic.....
In the words of the scarecrow from OZ "If I only had a brain"... You da Wiz...... Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy all named ranges in a sheet to seperate sheets
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy all named ranges in a sheet to seperate sheets
All This stuff is great... Thanks for the help everyone...
Chris |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy all named ranges in a sheet to seperate sheets
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy all named ranges in a sheet to seperate sheets
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink to named ranges and sheets in Excel not working in 2007 | Excel Discussion (Misc queries) | |||
trying to copy a worksheet containing named ranges to anotherworksheet | Excel Worksheet Functions | |||
Copy multiple sheets to seperate workbooks | Excel Discussion (Misc queries) | |||
Copying named ranges from one sheet to another | Excel Programming | |||
named ranges and copying sheets to another workbook | Excel Programming |