Command button code
 

I have a macro that I created and I have copied the code from it to the code
area for a command button and when I execute the command button it does not
work but if I execute the macro it does. see the code for both below, I
receive the error Selected method of range class failed

Macro:

Private Sub CommandButton2_Click()
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ChDir "\\nysyrfsv04\shared\UGTREAS\P & C"
Workbooks.Open Filename:= _
"\\nysyrfsv04\shared\UGTREAS\P & C\Current Third Party.xls"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
End Sub

Command:
Private Sub CommandButton3_Click()
Range("A2").Select
Workbooks.Open Filename:= _
"\\nysyrfsv04\shared\UGTREAS\P & C\Current Third Party.xls"
Range("A2:E2").Select ******Error here *******
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.Close
ActiveSheet.Paste
Range("A2").Select
ActiveWorkbook.Save
End Sub

Command button code
 

the way im doing it when i use button

rigth click on the button and do assign macro !!




taps54 wrote:
I have a macro that I created and I have copied the code from it to the code
area for a command button and when I execute the command button it does not
work but if I execute the macro it does. see the code for both below, I
receive the error Selected method of range class failed

Macro:

Private Sub CommandButton2_Click()
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ChDir "\\nysyrfsv04\shared\UGTREAS\P & C"
Workbooks.Open Filename:= _
"\\nysyrfsv04\shared\UGTREAS\P & C\Current Third Party.xls"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
End Sub

Command:
Private Sub CommandButton3_Click()
Range("A2").Select
Workbooks.Open Filename:= _
"\\nysyrfsv04\shared\UGTREAS\P & C\Current Third Party.xls"
Range("A2:E2").Select ******Error here *******
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.Close
ActiveSheet.Paste
Range("A2").Select
ActiveWorkbook.Save
End Sub

Command button code
 

That code should probably be in the worksheet code module that the button is
on.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"taps54" wrote in message
...
I have a macro that I created and I have copied the code from it to the
code
area for a command button and when I execute the command button it does
not
work but if I execute the macro it does. see the code for both below, I
receive the error Selected method of range class failed

Macro:

Private Sub CommandButton2_Click()
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ChDir "\\nysyrfsv04\shared\UGTREAS\P & C"
Workbooks.Open Filename:= _
"\\nysyrfsv04\shared\UGTREAS\P & C\Current Third Party.xls"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
End Sub

Command:
Private Sub CommandButton3_Click()
Range("A2").Select
Workbooks.Open Filename:= _
"\\nysyrfsv04\shared\UGTREAS\P & C\Current Third Party.xls"
Range("A2:E2").Select ******Error here *******
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.Close
ActiveSheet.Paste
Range("A2").Select
ActiveWorkbook.Save
End Sub

Command button code
 

I do not get the option to assign a macro. I am using excel 2003 and when I
right click on the button there is no option for assign a macro

" wrote:

the way im doing it when i use button

rigth click on the button and do assign macro !!




taps54 wrote:
I have a macro that I created and I have copied the code from it to the code
area for a command button and when I execute the command button it does not
work but if I execute the macro it does. see the code for both below, I
receive the error Selected method of range class failed

Macro:

Private Sub CommandButton2_Click()
Range("A1:E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ChDir "\\nysyrfsv04\shared\UGTREAS\P & C"
Workbooks.Open Filename:= _
"\\nysyrfsv04\shared\UGTREAS\P & C\Current Third Party.xls"
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A2").Select
End Sub

Command:
Private Sub CommandButton3_Click()
Range("A2").Select
Workbooks.Open Filename:= _
"\\nysyrfsv04\shared\UGTREAS\P & C\Current Third Party.xls"
Range("A2:E2").Select ******Error here *******
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.Close
ActiveSheet.Paste
Range("A2").Select
ActiveWorkbook.Save
End Sub