CODEWITHSUNDEEP
excelvba
XCELLGUY
XCELLGUY

Reputation: 319

For each loop through sheets with Selection.AutoFill

This doesn't compile.

Option Explicit
Sub test3()
    Dim sht As Worksheet

    For Each sht In Sheets
        sht.Range("C1").FormulaArray = "=A1&B1"
        sht.Range("C1").Select
        sht.Selection.AutoFill Destination:=Range("C1:C3"), Type:=xlFillDefault
    Next sht
End Sub

The error

Compile error: Method or data member not found

is on:

sht.Selection.AutoFill Destination:=Range("C1:C3"), Type:=xlFillDefault

I also tried

Option Explicit
Sub test3()
    Dim sht As Worksheet

    For Each sht In Sheets
        sht.Range("C1").FormulaArray = "=A1&B1"
        sht.Range("C1").Select
        Selection.AutoFill Destination:=Range("C1:C3"), Type:=xlFillDefault
    Next sht
End Sub

This can work depending on the sheet I have active. For example if I have sheets(1) active it works on Sheets(1) but then on Sheets(2) I get an error:

Run-time error '1004': Select method of Range class failed

Upvotes: 0

Views: 82

Answers (1)

mooseman
mooseman

Reputation: 2017

This will work for the activesheet (Using sheets array is referencing the active workbook).

For Each sht In Sheets
    sht.Range("C1").FormulaArray = "=A1&B1"
    sht.Range("C1").AutoFill Destination:=sht.Range("C1:C3"), Type:=xlFillDefault
Next sht

Upvotes: 3

Related Questions