macro does not work from commandbutton

  • Thread starter ellflocko via OfficeKB.com
  • Start date
E

ellflocko via OfficeKB.com

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
 
T

Tom Ogilvy

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
 
E

ellflocko via OfficeKB.com

That worked great!!! Thanks so much.
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
Click to expand...
 
Top