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

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

On May 11, 9:04 am, 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

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

" wrote:

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

On May 11, 9:04 am, 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

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

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

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).Of fset(1,
0).PasteSpecial xlPasteAll
Application.CutCopyMode = False
Next x
End Sub

regards
Paul

On May 11, 10:53 am, 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- Hide quoted text -

- Show quoted text -

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.

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" wrote in message
...
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

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


" wrote:

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).Of fset(1,
0).PasteSpecial xlPasteAll
Application.CutCopyMode = False
Next x
End Sub

regards
Paul

On May 11, 10:53 am, 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- Hide quoted text -

- Show quoted text -

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.

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.

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!

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

"IanKR" wrote:

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.

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/micro...7b243a 308e8d

"IanKR" wrote:

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!
Yes, I am sure, but anyway how can we check it , I jsut see all the name,
that I isert name deffine

Frank

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!
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

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

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

IanKR wrote:

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!

--

Dave Peterson

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
"Vergel Adriano" wrote:

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