loop through all named ranges

B

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?
 
T

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
 
Top