ExcelBanter - For Each ...next..

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - For Each ...next.. (https://www.excelbanter.com/excel-programming/389214-each-next.html)

Frank Situmorang

For Each ...next..

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 Situmorang

For Each ...next..

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

Halim

For Each ...next..

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

Frank Situmorang

For Each ...next..

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

[email protected]

For Each ...next..

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 -

IanKR

For Each ...next..

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

For Each ...next..

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

Frank Situmorang

For Each ...next..

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 -

IanKR

For Each ...next..

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

For Each ...next..

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

For Each ...next..

Frank Situmorang

For Each ...next..

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.

IanKR

For Each ...next..

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

Frank Situmorang

For Each ...next..

"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

For Each ...next..

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

Vergel Adriano

For Each ...next..

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

Dave Peterson

For Each ...next..

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

Frank Situmorang

For Each ...next..

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

All times are GMT +1. The time now is 05:11 PM.