Macro Error in Command Button

[Paul]

I have recorded a macro that runs fine when I use the Macro Run command, but
when I copy the macro text and assign it to a command button it gives an
error. The following is the macro:

It gets hung up on the 2nd line (Rows...)

Any idea why it would work as a macro and not when placed into a command
button?

Sheets("Jacobs-Planning").Select
Rows("1:343").Select
Selection.Clear
Range("B7").Select
ActiveWindow.SmallScroll Down:=-30
Range("A5").Select
Range("MasterData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Sheets("Master Sheet").Range("A2:A3"), CopyToRange:=Range("A6"),
Unique:= _
False
Cells.Select
With Selection.Font
.Name = "Calibri"
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("H6").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub

 

[Jon Peltier]

What kind of command button (Forms toolbar or Controls Toolbox)? Where did
you paste the code? What version of Excel is this?

- Jon

 

[Paul]

It was a control toolbox, but I changed it to a Forms Button at it appears
to work.

It is excel 2003.

I think this is solved.

Thanks

 

[Dave Peterson]

If this macro is assigned to a commandbutton placed on a worksheet (from the
control toolbox), then part of the problem is then unqualified ranges.

Those unqualified ranges (like rows("1:343") will refer to the sheet that owns
the code--not the activesheet. This is different than code placed in a general
module.

And since you can only select a range on a worksheet that is active, the kind of
thing blows up:

Sheets("Jacobs-Planning").Select
Rows("1:343").Select

This may work for you:

Option Explicit
Private Sub CommandButton1_Click()
With Worksheets("Jacobs-Planning")
.Rows("1:343").Clear 'use .clearcontents to keep the formatting

.Range("MasterData").AdvancedFilter _
Action:=xlFilterCopy, _
CriteriaRange:=Worksheets("Master Sheet").Range("A2:A3"), _
CopyToRange:=.Range("A6"), _
Unique:=False

With .Cells.Font
.Name = "Calibri"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With

End Sub

This line may have to change:

.Range("MasterData").AdvancedFilter _

to something like:

worksheets("Someothersheetnamehere").Range("MasterData").AdvancedFilter _

I wasn't sure what worksheet owned the MasterData range, so I guessed
"jacobs-planning".

 

[Jon Peltier]

I should have pointed out that I've written about these two ways to assign
macros to buttons:

http://peltiertech.com/WordPress/2008/03/12/how-to-assign-a-macro-to-a-button-or-shape/
http://peltiertech.com/WordPress/2008/03/13/how-to-assign-a-macro-to-an-activex-control/

- Jon