loop through all named ranges
 

I have a set of named ranges in a worksheet that are spread out
through the sheet. I need to loop through the ranges, with the
intention of copy the values and pasting them in another workbook.

Any suggestions?

loop through all named ranges
 

Dim rng as Range
Dim nm as Name
for each nm in Thisworkbook.Names
set rng = nothing
on Error Resume Next
set rng = nm.RefersToRange
On error goto 0
rng.copy Destination:=workbooks("New.xls"). _
Worksheets(1).Cells(rows.count,1).end(xlup)(2)
Next

--
Regards,
Tom Ogilvy


"Bert" wrote in message
om...
I have a set of named ranges in a worksheet that are spread out
through the sheet. I need to loop through the ranges, with the
intention of copy the values and pasting them in another workbook.

Any suggestions?

loop through all named ranges
 

Tom,
What does the (2) do on the next to last line of your code?
Thanks,
JMay

"Tom Ogilvy" wrote in message
...
Dim rng as Range
Dim nm as Name
for each nm in Thisworkbook.Names
set rng = nothing
on Error Resume Next
set rng = nm.RefersToRange
On error goto 0
rng.copy Destination:=workbooks("New.xls"). _
Worksheets(1).Cells(rows.count,1).end(xlup)(2)
Next

--
Regards,
Tom Ogilvy


"Bert" wrote in message
om...
I have a set of named ranges in a worksheet that are spread out
through the sheet. I need to loop through the ranges, with the
intention of copy the values and pasting them in another workbook.

Any suggestions?

loop through all named ranges
 

if offsets to the next cell below.

--
Regards,
Tom Ogilvy

"JMay" wrote in message
news:roWAc.2805$HN5.1380@lakeread06...
Tom,
What does the (2) do on the next to last line of your code?
Thanks,
JMay

"Tom Ogilvy" wrote in message
...
Dim rng as Range
Dim nm as Name
for each nm in Thisworkbook.Names
set rng = nothing
on Error Resume Next
set rng = nm.RefersToRange
On error goto 0
rng.copy Destination:=workbooks("New.xls"). _
Worksheets(1).Cells(rows.count,1).end(xlup)(2)
Next

--
Regards,
Tom Ogilvy


"Bert" wrote in message
om...
I have a set of named ranges in a worksheet that are spread out
through the sheet. I need to loop through the ranges, with the
intention of copy the values and pasting them in another workbook.

Any suggestions?

loop through all named ranges
 

Thanks Tom;

"Tom Ogilvy" wrote in message
...
if offsets to the next cell below.

--
Regards,
Tom Ogilvy

"JMay" wrote in message
news:roWAc.2805$HN5.1380@lakeread06...
Tom,
What does the (2) do on the next to last line of your code?
Thanks,
JMay

"Tom Ogilvy" wrote in message
...
Dim rng as Range
Dim nm as Name
for each nm in Thisworkbook.Names
set rng = nothing
on Error Resume Next
set rng = nm.RefersToRange
On error goto 0
rng.copy Destination:=workbooks("New.xls"). _
Worksheets(1).Cells(rows.count,1).end(xlup)(2)
Next

--
Regards,
Tom Ogilvy


"Bert" wrote in message
om...
I have a set of named ranges in a worksheet that are spread out
through the sheet. I need to loop through the ranges, with the
intention of copy the values and pasting them in another workbook.

Any suggestions?