For each loop through sheets with Selection.AutoFill

Question

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

I forgot to mention... if I step through the code... and I manually select each tab as the code moves from one sheet to the next... it works fantastically on all sheets.... — XCELLGUY, Aug 22 '19 at 17:00
You can’t select a sheet’s cell without selecting the sheet first. So above the line `sht.Range("C1").Select` you’d put `sht.Select` — Marcucciboy2, Aug 22 '19 at 17:12
That said, it’s recommended that you rewrite your function to not use `.Select` at all — Marcucciboy2, Aug 22 '19 at 17:12
Thank you!! Since everybody and their brother says "it’s recommended that you rewrite your function to not use .Select at all" I have basically no experience using ".Select" which explains why I didn't know/remember that I needed to select the sheet. That said, what is a good alternative to using select in this situation? — XCELLGUY, Aug 22 '19 at 17:20
[How to avoid using Select in Excel VBA](https://stackoverflow.com/a/10718179/2727437) — Marcucciboy2, Aug 22 '19 at 18:13

score 3 · Accepted Answer · answered Aug 22 '19 at 17:18

3

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

answered Aug 22 '19 at 17:18

mooseman

1,997
2
17
29

Thank you!! Much appreciated!! – XCELLGUY Aug 22 '19 at 17:21

For each loop through sheets with Selection.AutoFill

1 Answers1