loop through all named ranges

[Bert]

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?

 

[Tom Ogilvy]

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

 

[JMay]

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

 

[Tom Ogilvy]

if offsets to the next cell below.

 

[JMay]

Thanks Tom;

if offsets to the next cell below.