Hello Excel users and experts,

Excel 2002 SP3

I am using this incredibly complex copy macro in a worksheet. Took me weeks
to build it...(sarcasm intended!) For a couple weeks it did exactly what
you would expect, copies E2:M10 to E28:M36. No blinks, no jumps, no
nothing, just copies like you would expect at the click of a forms button.

Now, for a reason I cannot fathom, it copies to E28:M36 and that entire
range is selected with E28 the activecell and the sheet has moved down to
show the range!!!

What the h... am I overlooking? I must have my nose too close to my VB
editor to see what must be the obvious!

Sub CopyIt()
Range("E2:M10").Copy Range("E28")
End Sub

Thanks,
Howard

Hi Howard,

Your code makes no selections and I am unable to reproduce your problem.

As a matter of interest, what cell is active when you run the code?

---
Regards,
Norman



"L. Howard Kittle" wrote in message
...
Hello Excel users and experts,

Excel 2002 SP3

I am using this incredibly complex copy macro in a worksheet. Took me
weeks to build it...(sarcasm intended!) For a couple weeks it did exactly
what you would expect, copies E2:M10 to E28:M36. No blinks, no jumps, no
nothing, just copies like you would expect at the click of a forms button.

Now, for a reason I cannot fathom, it copies to E28:M36 and that entire
range is selected with E28 the activecell and the sheet has moved down to
show the range!!!

What the h... am I overlooking? I must have my nose too close to my VB
editor to see what must be the obvious!

Sub CopyIt()
Range("E2:M10").Copy Range("E28")
End Sub

Thanks,
Howard

Hi Norm,

No particular cell, mostly it would be a cell in the E2:M10 range. I tried
various different activecells in that range and results are the same.

I have probably 20 to 30 other macros in the sheet module and a regular
module, but none are associated with the copy macro nor do any of them
select that particular range.

Baffling!!

Thanks, for the response, I guess I'll have to live with it. Sure is
puzzling though.

Regards,
Howard

"L. Howard Kittle" wrote in message
...
Hello Excel users and experts,

Excel 2002 SP3

I am using this incredibly complex copy macro in a worksheet. Took me
weeks to build it...(sarcasm intended!) For a couple weeks it did exactly
what you would expect, copies E2:M10 to E28:M36. No blinks, no jumps, no
nothing, just copies like you would expect at the click of a forms button.

Now, for a reason I cannot fathom, it copies to E28:M36 and that entire
range is selected with E28 the activecell and the sheet has moved down to
show the range!!!

What the h... am I overlooking? I must have my nose too close to my VB
editor to see what must be the obvious!

Sub CopyIt()
Range("E2:M10").Copy Range("E28")
End Sub

Thanks,
Howard

Hi Howard,

Since the macro is a standalone, I would suggest that you comment all other
macros and retry your code.


---
Regards,
Norman



"L. Howard Kittle" wrote in message
. ..
Hi Norm,

No particular cell, mostly it would be a cell in the E2:M10 range. I
tried various different activecells in that range and results are the
same.

I have probably 20 to 30 other macros in the sheet module and a regular
module, but none are associated with the copy macro nor do any of them
select that particular range.

Baffling!!

Thanks, for the response, I guess I'll have to live with it. Sure is
puzzling though.

Regards,
Howard

"L. Howard Kittle" wrote in message
...
Hello Excel users and experts,

Excel 2002 SP3

I am using this incredibly complex copy macro in a worksheet. Took me
weeks to build it...(sarcasm intended!) For a couple weeks it did
exactly what you would expect, copies E2:M10 to E28:M36. No blinks, no
jumps, no nothing, just copies like you would expect at the click of a
forms button.

Now, for a reason I cannot fathom, it copies to E28:M36 and that entire
range is selected with E28 the activecell and the sheet has moved down to
show the range!!!

What the h... am I overlooking? I must have my nose too close to my VB
editor to see what must be the obvious!

Sub CopyIt()
Range("E2:M10").Copy Range("E28")
End Sub

Thanks,
Howard

Hi Norman,

Okay, took all macros out of the sheet module and moved to a spare
worksheet. The copy macro worked fine. Moved, one by one, macros back into
the sheet module and tested the copy after each and when the one below was
returned the copy problem returned.

But still a puzzle. This routine sets 9 ranges and then determines in which
range the activecell is in and then calls a macro pertinent to that range to
do stuff. If activecell is in "rng5" then it calls "Data5" and it does
stuff to "rng5". After that, a macro "ClearNumbersHrow" is called and it
does essentially the same thing but to a different set of ranges and a third
macro is called doing the same stuff to a third set of ranges.

The "Data1 thru 9" macros are a ...Find n, replace with "" and it's the
same with the other two ClearNumber... macros.

Norman, this is just a SUDOKU puzzle solver I made to test my programming
skills, so it really is not that important. But I do appreciate you
suggestion and am really stumped as to why these routines would flaw the
simple little copy macro.

I appreciate your help, but you must have bigger fish to fry.

Thanks a lot,
Howard

Sub ClearNumbersBox()

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
Dim rng8 As Range
Dim rng9 As Range

On Error Resume Next
Set rng1 = Intersect(ActiveCell, Range("Data1"))
Set rng2 = Intersect(ActiveCell, Range("Data2"))
Set rng3 = Intersect(ActiveCell, Range("Data3"))
Set rng4 = Intersect(ActiveCell, Range("Data4"))
Set rng5 = Intersect(ActiveCell, Range("Data5"))
Set rng6 = Intersect(ActiveCell, Range("Data6"))
Set rng7 = Intersect(ActiveCell, Range("Data7"))
Set rng8 = Intersect(ActiveCell, Range("Data8"))
Set rng9 = Intersect(ActiveCell, Range("Data9"))

On Error GoTo 0

If Not rng1 Is Nothing Then
Call Data1
ElseIf Not rng2 Is Nothing Then
Call Data2
ElseIf Not rng3 Is Nothing Then
Call Data3
ElseIf Not rng4 Is Nothing Then
Call Data4
ElseIf Not rng5 Is Nothing Then
Call Data5
ElseIf Not rng6 Is Nothing Then
Call Data6
ElseIf Not rng7 Is Nothing Then
Call Data7
ElseIf Not rng8 Is Nothing Then
Call Data8
ElseIf Not rng9 Is Nothing Then
Call Data9
Else
'do nothing
End If

ClearNumbersHrow
End Sub



"L. Howard Kittle" wrote in message
...
Hello Excel users and experts,

Excel 2002 SP3

I am using this incredibly complex copy macro in a worksheet. Took me
weeks to build it...(sarcasm intended!) For a couple weeks it did exactly
what you would expect, copies E2:M10 to E28:M36. No blinks, no jumps, no
nothing, just copies like you would expect at the click of a forms button.

Now, for a reason I cannot fathom, it copies to E28:M36 and that entire
range is selected with E28 the activecell and the sheet has moved down to
show the range!!!

What the h... am I overlooking? I must have my nose too close to my VB
editor to see what must be the obvious!

Sub CopyIt()
Range("E2:M10").Copy Range("E28")
End Sub

Thanks,
Howard

Hi Howard,

If you wish, send me your workbook.

norman_jones@NOSPAMbtconnectDOTcom

Delete'NOSPAM' and replace 'DOT' with a period (full stop).


---
Regards,
Norman


"L. Howard Kittle" wrote in message
...
Hi Norman,

Okay, took all macros out of the sheet module and moved to a spare
worksheet. The copy macro worked fine. Moved, one by one, macros back
into the sheet module and tested the copy after each and when the one
below was returned the copy problem returned.

But still a puzzle. This routine sets 9 ranges and then determines in
which range the activecell is in and then calls a macro pertinent to that
range to do stuff. If activecell is in "rng5" then it calls "Data5" and
it does stuff to "rng5". After that, a macro "ClearNumbersHrow" is called
and it does essentially the same thing but to a different set of ranges
and a third macro is called doing the same stuff to a third set of ranges.

The "Data1 thru 9" macros are a ...Find n, replace with "" and it's the
same with the other two ClearNumber... macros.



skills, so it really is not that important. But I do appreciate you
suggestion and am really stumped as to why these routines would flaw the
simple little copy macro.

I appreciate your help, but you must have bigger fish to fry.

Thanks a lot,
Howard

Sub ClearNumbersBox()

Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim rng7 As Range
Dim rng8 As Range
Dim rng9 As Range

On Error Resume Next
Set rng1 = Intersect(ActiveCell, Range("Data1"))
Set rng2 = Intersect(ActiveCell, Range("Data2"))
Set rng3 = Intersect(ActiveCell, Range("Data3"))
Set rng4 = Intersect(ActiveCell, Range("Data4"))
Set rng5 = Intersect(ActiveCell, Range("Data5"))
Set rng6 = Intersect(ActiveCell, Range("Data6"))
Set rng7 = Intersect(ActiveCell, Range("Data7"))
Set rng8 = Intersect(ActiveCell, Range("Data8"))
Set rng9 = Intersect(ActiveCell, Range("Data9"))

On Error GoTo 0

If Not rng1 Is Nothing Then
Call Data1
ElseIf Not rng2 Is Nothing Then
Call Data2
ElseIf Not rng3 Is Nothing Then
Call Data3
ElseIf Not rng4 Is Nothing Then
Call Data4
ElseIf Not rng5 Is Nothing Then
Call Data5
ElseIf Not rng6 Is Nothing Then
Call Data6
ElseIf Not rng7 Is Nothing Then
Call Data7
ElseIf Not rng8 Is Nothing Then
Call Data8
ElseIf Not rng9 Is Nothing Then
Call Data9
Else
'do nothing
End If

ClearNumbersHrow
End Sub



"L. Howard Kittle" wrote in message
...
Hello Excel users and experts,

Excel 2002 SP3

I am using this incredibly complex copy macro in a worksheet. Took me
weeks to build it...(sarcasm intended!) For a couple weeks it did
exactly what you would expect, copies E2:M10 to E28:M36. No blinks, no
jumps, no nothing, just copies like you would expect at the click of a
forms button.

Now, for a reason I cannot fathom, it copies to E28:M36 and that entire
range is selected with E28 the activecell and the sheet has moved down to
show the range!!!

What the h... am I overlooking? I must have my nose too close to my VB
editor to see what must be the obvious!

Sub CopyIt()
Range("E2:M10").Copy Range("E28")
End Sub

Thanks,
Howard

Thanks, Norman. One on the way some time ago.

"L. Howard Kittle" wrote in message
...
Hello Excel users and experts,

Excel 2002 SP3

I am using this incredibly complex copy macro in a worksheet. Took me
weeks to build it...(sarcasm intended!) For a couple weeks it did exactly
what you would expect, copies E2:M10 to E28:M36. No blinks, no jumps, no
nothing, just copies like you would expect at the click of a forms button.

Now, for a reason I cannot fathom, it copies to E28:M36 and that entire
range is selected with E28 the activecell and the sheet has moved down to
show the range!!!

What the h... am I overlooking? I must have my nose too close to my VB
editor to see what must be the obvious!

Sub CopyIt()
Range("E2:M10").Copy Range("E28")
End Sub

Thanks,
Howard

Hi Howard,

Thanks, Norman. One on the way some time ago.

Nothing here yet. You might check the deciphering of the email address.


---
Regards,
Norman


"L. Howard Kittle" wrote in message
...
Thanks, Norman. One on the way some time ago.

I deleted one too many letters on the first try. Hopefully, one on the way.

"L. Howard Kittle" wrote in message
...
Hello Excel users and experts,

Excel 2002 SP3

I am using this incredibly complex copy macro in a worksheet. Took me
weeks to build it...(sarcasm intended!) For a couple weeks it did exactly
what you would expect, copies E2:M10 to E28:M36. No blinks, no jumps, no
nothing, just copies like you would expect at the click of a forms button.

Now, for a reason I cannot fathom, it copies to E28:M36 and that entire
range is selected with E28 the activecell and the sheet has moved down to
show the range!!!

What the h... am I overlooking? I must have my nose too close to my VB
editor to see what must be the obvious!

Sub CopyIt()
Range("E2:M10").Copy Range("E28")
End Sub

Thanks,
Howard

Hi Howard,

I have sent you a revised workbook which works, I think, as you wish.

I have moved non-event code from the sheet module to your standard module
and reassigned the buttons to point to the changed macro locations.

I have commented out a selection line in the Worksheet_Change event because
(a) I could see no obvious need for the selection and (b) because it was
this line that was causing your observed problem. In case you do need the
selection elsewhere, I have amended the copy macro to turn events off at the
start and turn events back on at the end of the code. Whatever you do with
the selection line in the event code, you can leave the Copy macro in its
amended state.


---
Regards,
Norman



"L. Howard Kittle" wrote in message
...
I deleted one too many letters on the first try. Hopefully, one on the
way.

Hey gang,

Norman nailed it!! And he took the time for a frivolous, albeit a curious,
programming snafu.

Many thanks, and Merry Christmas!!!

Regards,
Howard

"L. Howard Kittle" wrote in message
...
Hello Excel users and experts,

Excel 2002 SP3

I am using this incredibly complex copy macro in a worksheet. Took me
weeks to build it...(sarcasm intended!) For a couple weeks it did exactly
what you would expect, copies E2:M10 to E28:M36. No blinks, no jumps, no
nothing, just copies like you would expect at the click of a forms button.

Now, for a reason I cannot fathom, it copies to E28:M36 and that entire
range is selected with E28 the activecell and the sheet has moved down to
show the range!!!

What the h... am I overlooking? I must have my nose too close to my VB
editor to see what must be the obvious!

Sub CopyIt()
Range("E2:M10").Copy Range("E28")
End Sub

Thanks,
Howard