Strange Macro Behavior

[MikeG]

I am getting a strange thing.

Using Excel's "Record Macro" wizard, I generated the following code:

Sub Macro10()
'
' Macro10 Macro
Range("B7").Select
Selection.Copy
Sheets("Quotes Database").Select
Range("J5").Select
ActiveSheet.Paste
End Sub

If I then run Macro10 it works fine.

However, if I go to Developer on the Ribbon and then insert a rectangular command button and assign the same code to it, then when I click on the button, an error 1004 is generated.

If I insert a regular shape and assign the code, the macro works OK.

Why does it not work for the inserted command button?

Does anyone know what is going on? Can you replicate this? I'm using 2007.

Thanks,

MikeG

 

[jmthompson]

You haven't specified what sheet you are copying from, could be the issue.

 

[MikeG]

Thanks Jen.

But the macro works fine when I run it as Macro10, or from a regular shape. The same code does not work on an inserted command button - that is my issue.

MikeG

 

[Jonmo1]

code behaves differently depending on how it's called...

To be sure to avoid problems like this it is usually best to explicitly name the sheets involved in your code.
And to avoid Selecting, it is almost never necessary to select a sheet/range in order to manipulate it.

Try it like this

Sub Macro10()
'
' Macro10 Macro
Sheets("SourceSheet").Range("B7").Copy Destination:=Sheets("Quotes Database").Range("J5")
End Sub

Adjust the SourceSheet to reflect the sheet that you are copying from.


Hope that helps.

 

[p45cal]

insert a rectangular command button

an ActiveX button I take it

and assign the same code to it

meaning you copied the code into the CommandButton_Click event handler

in which case, the event handler code is in a sheet code-module and unqualified ranges refer to that sheet, once you've activated another sheet, you can't then select a cell on the first sheet without an error being thrown.

Solution, either:

Range("B7").Select
Selection.Copy
With Sheets("Quotes Database")
    .Select
    .Range("J5").Select
    .Paste
End With

or just :

Range("B7").Copy Sheets("Quotes Database").Range("J5")

 

[MikeG]

Thanks guys - it was driving me nuts!

MikeG