excel Marco question ... need help please
 

Good afternoon all,

I am having a problem converting a marco to a command button. When I run
this marco .... TOOLS, MARCO, MACROS..., (name).. RUN , the marco runs
correct. When I copy clip the marco to the View Code in the command button,
it does not work. Below is a attached file that might be able to explain it
better. Please help, or what can I do to fix.

Thanks,
ALP

This is what I have in the accutal MARCO. (this works, when I run the marco)

Dim myRange As Range
Set myRange = Worksheets("Work").Range("L1")

If myRange = 1 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

ElseIf myRange = 2 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

This is what happens when I try to set this marco to be on my
CommandButton1. (doesnt work)

Private Sub CommandButton1_Click()

Dim myRange As Range
Set myRange = Worksheets("Work").Range("L1")

If myRange = 1 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select


ElseIf myRange = 2 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("D4").Select ................ this is what is highlighted as error
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select


My error message is:

Run-Time error '1004':
Select Method of Range class failed.

Try creating your button from the Forms toolbar, rather than the Visual
Basic toolbar. When you create the button from the Forms toolbar
you'll get the option to assign a macro to it.

Hi ALP

I tried your code and it executes well in a command button as well as a
toolbar button.

Shafiee.

"ALP" wrote in message
...
Good afternoon all,

I am having a problem converting a marco to a command button. When I run
this marco .... TOOLS, MARCO, MACROS..., (name).. RUN , the marco runs
correct. When I copy clip the marco to the View Code in the command
button,
it does not work. Below is a attached file that might be able to explain
it
better. Please help, or what can I do to fix.

Thanks,
ALP

This is what I have in the accutal MARCO. (this works, when I run the
marco)

Dim myRange As Range
Set myRange = Worksheets("Work").Range("L1")

If myRange = 1 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

ElseIf myRange = 2 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

This is what happens when I try to set this marco to be on my
CommandButton1. (doesn't work)

Private Sub CommandButton1_Click()

Dim myRange As Range
Set myRange = Worksheets("Work").Range("L1")

If myRange = 1 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select


ElseIf myRange = 2 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("D4").Select ................ this is what is highlighted as error
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select


My error message is:

Run-Time error '1004':
Select Method of Range class failed.

Dave,

Thank you very much for your help. This does work. I did not know how easy
is could be, I guess I was trying to do too much with it.

Thanks again,
ALP

"Dave O" wrote:

Try creating your button from the Forms toolbar, rather than the Visual
Basic toolbar. When you create the button from the Forms toolbar
you'll get the option to assign a macro to it.