macro does not work from commandbutton

I have the following macro that works just fine from the toolbar but when I
attach the code to a newly created commandbutton, it runs into a "run-time
error '1004' select method of range-class failed" and the debugger highlights
the "Cells.Select" after opening the g: file. I have set the
takefocusonclick to false but that still does me no good. Anyone out there
that can help me. I am fairly new a VB and can't solve this one on my own.
Thanks.

ChDir "g:\"
Workbooks.Open Filename:="g:\morning packet.xls"
Cells.Select
Selection.Copy
Windows("SFL Journal 2.xls").Activate
Sheets("Morning Packet Import").Select
Cells.Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-117
Range("R34:T34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "-14.27"
Range("R68:T68").Select
ActiveCell.FormulaR1C1 = "-2.68"
Range("E102:F102").Select
ActiveCell.FormulaR1C1 = "$58.57 "
Range("L102:O102").Select
ActiveCell.FormulaR1C1 = "65.57"
Range("V102:X102").Select
ActiveCell.FormulaR1C1 = "$89.31 "
Range("AD102:AF102").Select
ActiveCell.FormulaR1C1 = "$46.83 "
Range("AM102:AO102").Select
ActiveCell.FormulaR1C1 = "1.91"
Range("E136:F136").Select
ActiveCell.FormulaR1C1 = "$57.00 "
Range("L136:O136").Select
ActiveCell.FormulaR1C1 = "57.78"
Range("V136:X136").Select
ActiveCell.FormulaR1C1 = "$98.51 "
Range("AD136:AF136").Select
ActiveCell.FormulaR1C1 = "$49.18 "
Range("AM136:AO136").Select
ActiveCell.FormulaR1C1 = "2"
Sheets("Journal").Select
ActiveWindow.SmallScroll Down:=-6
Windows("morning packet.xls").Activate
ActiveWindow.Close
End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1

ChDir "g:\"
Workbooks.Open Filename:="g:\morning packet.xls"
With ActiveSheet
.Cells.Select
Selection.Copy
End with
Windows("SFL Journal 2.xls").Activate
With Sheets("Morning Packet Import")
.Select
.Cells.Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-117
.Range("R34:T34").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "-14.27"
.Range("R68:T68").Select
ActiveCell.FormulaR1C1 = "-2.68"
.Range("E102:F102").Select
ActiveCell.FormulaR1C1 = "$58.57 "
.Range("L102:O102").Select
ActiveCell.FormulaR1C1 = "65.57"
.Range("V102:X102").Select
ActiveCell.FormulaR1C1 = "$89.31 "
.Range("AD102:AF102").Select
ActiveCell.FormulaR1C1 = "$46.83 "
.Range("AM102:AO102").Select
ActiveCell.FormulaR1C1 = "1.91"
.Range("E136:F136").Select
ActiveCell.FormulaR1C1 = "$57.00 "
.Range("L136:O136").Select
ActiveCell.FormulaR1C1 = "57.78"
.Range("V136:X136").Select
ActiveCell.FormulaR1C1 = "$98.51 "
.Range("AD136:AF136").Select
ActiveCell.FormulaR1C1 = "$49.18 "
.Range("AM136:AO136").Select
ActiveCell.FormulaR1C1 = "2"
End With
Sheets("Journal").Select
ActiveWindow.SmallScroll Down:=-6
Windows("morning packet.xls").Activate
ActiveWindow.Close
End Sub

--
Regards,
Tom Ogilvy


"ellflocko via OfficeKB.com" wrote:

> I have the following macro that works just fine from the toolbar but when I
> attach the code to a newly created commandbutton, it runs into a "run-time
> error '1004' select method of range-class failed" and the debugger highlights
> the "Cells.Select" after opening the g: file. I have set the
> takefocusonclick to false but that still does me no good. Anyone out there
> that can help me. I am fairly new a VB and can't solve this one on my own.
> Thanks.
>
> ChDir "g:\"
> Workbooks.Open Filename:="g:\morning packet.xls"
> Cells.Select
> Selection.Copy
> Windows("SFL Journal 2.xls").Activate
> Sheets("Morning Packet Import").Select
> Cells.Select
> ActiveSheet.Paste
> ActiveWindow.SmallScroll Down:=-117
> Range("R34:T34").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "-14.27"
> Range("R68:T68").Select
> ActiveCell.FormulaR1C1 = "-2.68"
> Range("E102:F102").Select
> ActiveCell.FormulaR1C1 = "$58.57 "
> Range("L102:O102").Select
> ActiveCell.FormulaR1C1 = "65.57"
> Range("V102:X102").Select
> ActiveCell.FormulaR1C1 = "$89.31 "
> Range("AD102:AF102").Select
> ActiveCell.FormulaR1C1 = "$46.83 "
> Range("AM102:AO102").Select
> ActiveCell.FormulaR1C1 = "1.91"
> Range("E136:F136").Select
> ActiveCell.FormulaR1C1 = "$57.00 "
> Range("L136:O136").Select
> ActiveCell.FormulaR1C1 = "57.78"
> Range("V136:X136").Select
> ActiveCell.FormulaR1C1 = "$98.51 "
> Range("AD136:AF136").Select
> ActiveCell.FormulaR1C1 = "$49.18 "
> Range("AM136:AO136").Select
> ActiveCell.FormulaR1C1 = "2"
> Sheets("Journal").Select
> ActiveWindow.SmallScroll Down:=-6
> Windows("morning packet.xls").Activate
> ActiveWindow.Close
> End Sub
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200605/1
>

That worked great!!! Thanks so much.

ellflocko wrote:
>I have the following macro that works just fine from the toolbar but when I
>attach the code to a newly created commandbutton, it runs into a "run-time
>error '1004' select method of range-class failed" and the debugger highlights
>the "Cells.Select" after opening the g: file. I have set the
>takefocusonclick to false but that still does me no good. Anyone out there
>that can help me. I am fairly new a VB and can't solve this one on my own.
>Thanks.
>
>ChDir "g:\"
> Workbooks.Open Filename:="g:\morning packet.xls"
> Cells.Select
> Selection.Copy
> Windows("SFL Journal 2.xls").Activate
> Sheets("Morning Packet Import").Select
> Cells.Select
> ActiveSheet.Paste
> ActiveWindow.SmallScroll Down:=-117
> Range("R34:T34").Select
> Application.CutCopyMode = False
> ActiveCell.FormulaR1C1 = "-14.27"
> Range("R68:T68").Select
> ActiveCell.FormulaR1C1 = "-2.68"
> Range("E102:F102").Select
> ActiveCell.FormulaR1C1 = "$58.57 "
> Range("L102:O102").Select
> ActiveCell.FormulaR1C1 = "65.57"
> Range("V102:X102").Select
> ActiveCell.FormulaR1C1 = "$89.31 "
> Range("AD102:AF102").Select
> ActiveCell.FormulaR1C1 = "$46.83 "
> Range("AM102:AO102").Select
> ActiveCell.FormulaR1C1 = "1.91"
> Range("E136:F136").Select
> ActiveCell.FormulaR1C1 = "$57.00 "
> Range("L136:O136").Select
> ActiveCell.FormulaR1C1 = "57.78"
> Range("V136:X136").Select
> ActiveCell.FormulaR1C1 = "$98.51 "
> Range("AD136:AF136").Select
> ActiveCell.FormulaR1C1 = "$49.18 "
> Range("AM136:AO136").Select
> ActiveCell.FormulaR1C1 = "2"
> Sheets("Journal").Select
> ActiveWindow.SmallScroll Down:=-6
> Windows("morning packet.xls").Activate
> ActiveWindow.Close
>End Sub

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200605/1