Please help...Assigning Macros to command buttons

I have tried for hours to effectively assign a macro to a command button on the first page of my 19 page workbook. Basically, I have recorded a Marco to delete the contents of specific cell over several spreadsheets. I have pasted this macro to the Code area for the command button in VB.

Private Sub Macro1_Click()
ActiveWindow.SmallScroll Down:=-25
Range("B5:I5").Select
Selection.ClearContents
Range("A9").Select
Selection.ClearContents
Range("B10:H10").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=8
Range("A15").Select
Selection.ClearContents
Range("B16:H16").Select
Selection.ClearContents
Range("B25:H25").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=16
Range("B31:H31").Select
Selection.ClearContents
Sheets("Last Year Sales").Select
ActiveWindow.SmallScroll Down:=-13
Range("A4").Select
Selection.ClearContents
Range("B5:H5").Select
Selection.ClearContents
Range("B12:H12").Select
Selection.ClearContents
Range("B19:B27").Select
Selection.ClearContents
Range("B29:B33").Select
Selection.ClearContents
End Sub

Please help. if you need any other info about the workbook let me know

Plus, you can tidy up your code quite a lot. This code below does the same as your code.

Private Sub Macro1_Click()
Range("B5:I5").ClearContents
Range("A9").ClearContents
Range("B10:H10").ClearContents
Range("A15").ClearContents
Range("B16:H16").ClearContents
Range("B25:H25").ClearContents
Range("B31:H31").ClearContents
Sheets("Last Year Sales").Select
Range("A4").ClearContents
Range("B5:H5").ClearContents
Range("B12:H12").ClearContents
Range("B19:B27").ClearContents
Range("B29:B33").ClearContents
End Sub

Originally Posted by Mallycat

Plus, you can tidy up your code quite a lot. This code below does the same as your code.

Private Sub Macro1_Click()
Range("B5:I5").ClearContents
Range("A9").ClearContents
Range("B10:H10").ClearContents
Range("A15").ClearContents
Range("B16:H16").ClearContents
Range("B25:H25").ClearContents
Range("B31:H31").ClearContents
Sheets("Last Year Sales").Select
Range("A4").ClearContents
Range("B5:H5").ClearContents
Range("B12:H12").ClearContents
Range("B19:B27").ClearContents
Range("B29:B33").ClearContents
End Sub

I tried Mallycats code in a standard module, the only way I could assign this macro to a button was to remove the private part of the sub routine. after that I was able to use the customise toolbar command and create a new button on the toolbar that I could assign this macro to. I prefer this to putting a button on a particular sheet because you can get to the button from any sheet, not just the one sheet.

Sub Macro1_Click()
Range("B5:I5").ClearContents
Range("A9").ClearContents
Range("B10:H10").ClearContents
Range("A15").ClearContents
Range("B16:H16").ClearContents
Range("B25:H25").ClearContents
Range("B31:H31").ClearContents
Sheets("Last Year Sales").Select
Range("A4").ClearContents
Range("B5:H5").ClearContents
Range("B12:H12").ClearContents
Range("B19:B27").ClearContents
Range("B29:B33").ClearContents
End Sub[/QUOTE]

I hope this was helpfull

whooops, yes you need to remove the private bit