Run Time Error '1004': Select method of Range Class failed VBA 2010

1
votes

I am trying to filldown formulas in a sheet. The code I am using is a recorded macro and it works fine until I connect it to a button. When I do so, it gives a

"Run Time Error '1004': Select method of Range Class failed"

Here is the code and I can see nothing wrong with it. When I hit debug it highlights the second line

Private Sub CommandButton2_Click()

Sheets("DB2 Totbel").Select

Range("B2:D2").Select

Selection.AutoFill Destination:=Range("B2:D15000"), Type:=xlFillDefault

Range("B2:D15000").Select

picture of code in VBA

    enter image description here

2
excelvba

2 Answers

0
votes

There is some ambiguity as to the parent worksheet of the B2:D2 and B2:D15000 ranges.

Private Sub CommandButton2_Click()
    Application.Calculation = xlManual
    With Sheets("DB2 Totbel")
        .Range("B2:D2").AutoFill Destination:=.Range("B2:D15000"), Type:=xlFillDefault
        'uncomment these is you need to activate the DB2 Totbel worksheet
        'and select B2:D15000
        '.Activate
        '.Range("B2:D15000").Select
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

Alternate with the Range.FillDown method.

Private Sub CommandButton2_Click()
    Application.Calculation = xlManual
    With Sheets("DB2 Totbel")
        .Range("B2:D15000").FillDown
    End With
    Application.Calculation = xlCalculationAutomatic
End Sub

Recorded macros are very verbose and make too much use of the .Select and .Activate commands. See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

0
votes
  Private Sub CommandButton2_Click()

    Application.Calculation = xlCalculationManual

        With Sheets("DB2 Totbel")
            .Range("B2:D2").AutoFill Destination:=.Range("B2:D15000"), Type:=xlFillDefault
        End With

    End Sub