For Each ...next..

[Frank Situmorang]

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

 

[paul.robinson]

Sorry, I've no idea at all what you want.
regards
Paul

 

[Frank Situmorang]

Paul:

My macro stucked in the Range.Copy Stage, I do not know why. could you
explain whiy after finishing copy the named ranges it stuched there?

Sorry maybe my English is not good, because we seldom use English in
Indonesia.

Thanks in advance,


Frank Situmorang

 

[Halim]

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")

 

[Frank Situmorang]

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

 

[paul.robinson]

Hi
try
' 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").Range("a65000").End(xlUp).Offset(1,
0).PasteSpecial xlPasteAll
Application.CutCopyMode = False
Next x
End Sub

regards
Paul

 

[IanKR]

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.

 

[NickHK]

Frank,
Not all Names refer to a range.
Could a formula or constant, or one of the "_***" system names that seem to
occur sometimes.
Add some error handling.

NickHK

 

[Frank Situmorang]

The sampe problem persists Paul. Just stuck in the same statement"
Range(x).copy. If you still have any way out Paul, I appreciate

Thanks

Frank

 

[IanKR]

The sampe problem persists Paul. Just stuck in the same statement"

Range(x).copy. If you still have any way out Paul, I appreciate

Thanks

Frank

Frank

See my and NickHK's posts sent earlier.

 

[Frank Situmorang]

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]

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

Yes, but are you certain there isn't also a name that *doesn't* refer to a
range?

PS: Please don't top-post!

 

[Frank Situmorang]

I have seen it IanKR, but what kind of Error handling should I make

 

[IanKR]

I have seen it IanKR, but what kind of Error handling should I make

A quick search on Google Groups produced this:

http://groups.google.com/group/micr...08e8d?lnk=st&q=&rnum=3&hl=en#bd567b243a308e8d

 

[Frank Situmorang]

Yes, but are you certain there isn't also a name that *doesn't* refer to a
range?

PS: Please don't top-post!

Yes, I am sure, but anyway how can we check it , I jsut see all the name,
that I isert name deffine

Frank

 

[IanKR]

IanKR: I have checked all my defined name ranges it's ok that is why

Yes, I am sure, but anyway how can we check it , I jsut see all the
name, that I isert name deffine

Frank

I don't think Insert | Name | Define shows hidden names. Try Jan Karel
Pieterse's Name Manager:

http://www.bmsltd.ie/MVP/Default.htm

 

[Vergel Adriano]

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.

 

[Dave Peterson]

Most people in the public excel newsgroups top post. Yes, we break usenet
etiquette.

 

[Frank Situmorang]

Thanks Vergel, we can see now that there is Print titile that I do not
realize it is a name. Anyway how can I print if I put a print titile in the
page set-up if it becomes a range name.

You have any idea?, or should I change my macro module?, It works perfecly
now.

Frank