CODEWITHSUNDEEP
vbaexcelmacros
Katy Torres
Katy Torres

Reputation: 137

Macros run fine when ran individually but not when called one after another by button

I have a series of Macros that I want to run to Autofill sheets when I press a button.

The first Macro called will AutoFill columns to the range of a specific column in that sheet, it runs perfectly fine. The rest of the sheets should be filled to the 100th row with whatever is on their row 2.

This runs well when run one by one but when I press the button to run them all together ALL of the macros are applied to Sheet1 only. Instead of to the sheets I had specified.

Also, It would be much better if all these were DownFilled rather than autofilled since the numbers get changed when I Autofill them and I need them to be repeats not increments.

Private Sub CommandButton1_Click()    
Fill_It_Down1
Fill_It_Down2
Fill_It_Down3
End Sub

These are the macros I have written.

Sub Fill_It_Down1()
Dim source As Worksheet
Set source = Sheets("Sheet1")
Range("D2:ZZ2").AutoFill destination:=Range("D2:ZZ" & Range("B" & Rows.Count).End(xlUp).Row)
End Sub

Sub Fill_It_Down2()
Dim source As Worksheet
Set source = Sheets("Sheet2")
Range("C2:ZZ2").AutoFill destination:=Range("C2:ZZ2" & 100)
End Sub

Sub Fill_It_Down3()
Dim source As Worksheet
Set source = Sheets("Sheet3")
Range("A2:ZZ2").AutoFill destination:=Range("A2:ZZ2" & 100)
End Sub

Upvotes: 1

Views: 934

Answers (2)

Comintern
Comintern

Reputation: 22185

Range is a property of a worksheet, so if your code is in a Sheet module, you need to explicitly reference the target worksheet:

Sub Fill_It_Down1()
Dim source As Worksheet
Set source = Sheets("Sheet1")
With source
    .Range("D2:ZZ2").AutoFill Destination:=.Range("D2:ZZ" & .Range("B" & .Rows.Count).End(xlUp).Row)
End With
End Sub

Sub Fill_It_Down2()
Dim source As Worksheet
Set source = Sheets("Sheet2")
source.Range("C2:ZZ2").AutoFill Destination:=source.Range("C2:ZZ2" & 100)
End Sub

Sub Fill_It_Down3()
Dim source As Worksheet
Set source = Sheets("Sheet3")
source.Range("A2:ZZ2").AutoFill Destination:=source.Range("A2:ZZ2" & 100)
End Sub

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166126

You need to make sure you're qualifying all of your Range calls with a worksheet object:

Sub Fill_It_Down1()

    With Sheets("Sheet1")
        .Range("D2:ZZ2").AutoFill destination:= _
               .Range("D2:ZZ" & .Range("B" & .Rows.Count).End(xlUp).Row)
    End with

End Sub

Upvotes: 2

Related Questions