IanKR: I have checked all my defined name ranges it's ok that is why all the
ranges were copied correctly.
I appreciate your thought.

Thanks

Frank


"IanKR" wrote:

Dear experts:

How can we stop the looping using For each... ..next. It worked for me
perfectly, but it stuck in the Range x. Copy after it finished
copying all my defined range name.

Here is my macro:
Sub AutoShape7_Click()
' Macro recorded 5/10/2007 by Frank
'
'
ActiveWindow.SmallScroll ToRight:=-5
Range("A9:Y1714").Select
Selection.ClearContents
Dim x As Object
For Each x In ActiveWorkbook.Names
Range(x).Copy
Sheets("SUMMARYWBLA").Select
Range("a65000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll
Next x
End Sub
Many thanks to all of you that had helped me

Frank

Just a thought - do all the names in the active workbook refer to range
addresses? It's possible that your code is falling over at a name that
doesn't refer to a range. I got your code to work in a workbook where all
names refer to range addresses, but it produced runtime error 1004 when I
introduced a name that referred to a formula string, rather than a range
address, because "Range(<formula string)" is meaningless.