Multiple worksheets autofill error 1004

Question

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".

This is just a very trivial example. To be more specific, suppose the cell(1,1) has value 9, then autofilling onto Range(Cells(1, 1), Cells(2, 1) should make the cell(2,1) has the same value as cell(1,1) which is 9. The problem doesn't occur when executing the first line, but on the second. — PAK S, Jul 24 '15 at 06:54
If your Activesheet is Sheet2 when executing the code, your first line will cause the problem too. — kelvin 004, Jul 24 '15 at 07:03

score 1 · Accepted Answer · answered Jul 24 '15 at 06:50

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.

score 0 · Answer 2 · answered Jul 24 '15 at 06:55

0

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))

answered Jul 24 '15 at 06:55

kelvin 004

413
2
7

See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). – Jul 24 '15 at 06:56
I have tried this method. It probably works on other excel version. But mine (2013) shows error 1004 somehow. It seems the vba community really want to avoid using **.Activate**. And Jeeped's answer solves the problem nicely. – PAK S Jul 27 '15 at 01:07

Multiple worksheets autofill error 1004

2 Answers2