Frank,

Reproduce the error and when Excel takes you to debug mode, go to the
immediate widow (press CTRL+G) and type this in

?x.name

Press ENTER and it should give you the name of the named range that's giving
you problems. You can then probably figure out what the problem is or post
back what you find out.


--
Hope that helps.

Vergel Adriano


"Frank Situmorang" wrote:

No.. Halim, the purpose of it is to copy the named ranges to one summary
sheet. I worked perfectly, meaning all the ranges from 7 sheets had been
copied to the one Summary sheet, but there is an error Runtime 1004 and I
have the debug it and see it stucked ( showing yellow color) on statment
Range.copy

I appreciate your input and I expect your idea.

Thanks

Frank


"Halim" wrote:

Frank,

I think the problem is
Sheets("SUMMARYWBLA").Select
during your loop because the program recognize x as names in the
sheet("blabla")
instead of Sheets("SUMMARYWBLA")

--

Regards,

Halim


"Frank Situmorang" 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