CODEWITHSUNDEEP
vbaexcelexcel-2013autofill
PAK S
PAK S

Reputation: 13

Multiple worksheets autofill error 1004

I want to create a button that does autofills in multiple worksheets. But it seems I could only do autofill one sheet at a time... Here is the code:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 1)).AutoFill Destination:=Sheets("Sheet1").Range(Cells(1, 1), Cells(2, 1))
Sheets("Sheet2").Range(Cells(1, 1), Cells(1, 1)).AutoFill Destination:=Sheets("Sheet2").Range(Cells(1, 1), Cells(2, 1))
End Sub

Simple at that. If I break it down into two different buttons, they work just fine. I've tried Worksheets().Activate, but it doesn't help. (most people don't recommend activate anyways) Also tried writing Sub but the same problem persist as "error 1004".

Upvotes: 1

Views: 135

Answers (2)

kelvin 004
kelvin 004

Reputation: 423

It works when I added Activate:

Sheets("Sheet1").Activate  'added
Sheets("Sheet1").Range(Cells(1, 1), Cells(1, 1)).AutoFill _
        Destination:=Sheets("Sheet1").Range(Cells(1, 1), Cells(2, 1))

Sheets("Sheet2").Activate  'added
Sheets("Sheet2").Range(Cells(1, 1), Cells(1, 1)).AutoFill _
    Destination:=Sheets("Sheet2").Range(Cells(1, 1), Cells(2, 1))

Upvotes: 0

user4039065
user4039065

Reputation:

You've ran into a common coding error where the Range.Cells property inside the Range object do not have their parent explicitly defined.

Private Sub CommandButton1_Click()
    With Sheets("Sheet1")
        .Range(.Cells(1, 1), .Cells(1, 1)).AutoFill _
          Destination:=.Range(.Cells(1, 1), .Cells(2, 1))
    End With
    With Sheets("Sheet2")
        .Range(.Cells(1, 1), .Cells(1, 1)).AutoFill _
            Destination:=.Range(.Cells(1, 1), .Cells(2, 1))
    End With
End Sub

Note .Range(.Cells(1, 1), .Cells(1, 1)) and not .Range(Cells(1, 1), Cells(1, 1)). Your original was trying to define a range containing the cells on another worksheet.

The With ... End With statement can make the assignment of the parent worksheet a lot easier and doesn't obfuscate what you are trying to accomplish.

Upvotes: 1

Related Questions