Strange behaviour VBA Autofill

[mellymelle]

Have a block of code that utilized .AutoFill several times. One of them returned a run-time error 1004 autofill method of range class failed. Couldn't for the life of me figure out why the other autofill codes weren't generating errors when they were in the exact same format (and were executing on the workbook - formulas were filled down as coded), so gave up and used copy paste instead on the code generating the error. The next run, another autofill code line that was executing fine before turned up a 1004 error. I didn't change the line in question at all, why would it work before and not now?

Here's a sample of the code, the original autofill line is included (but commented out) for illustration:

    Sheet3.Columns("A:A").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Sheet3.Range("A1").Formula = "Account #"
    Sheet3.Range("A2").Formula = "=LEFT(TRIM(RC[8]),3) & MID(TRIM(RC[8]),5,4) & RIGHT(TRIM(RC[8]),1)"
    If ALastRow > 2 Then
        Sheet3.Range("A2").Copy Sheet3.Range("A3:A" & ALastRow)
'        Sheet3.Range("A2").AutoFill Destination:=Range("A2:A" & ALastRow), Type:=xlFillDefault
    End If

My workaround is working fine, but I'm perplexed. Please share your thoughts, thanks in advance.

 

[StephenCrump]

Try: Sheet3.Range("A2").AutoFill Destination:=Sheet3.Range("A2:A" & ALastRow), Type:=xlFillDefault

(Without this qualification, your code will give a run-time error 1004 if Sheet3 is not the Active Sheet)

 

[StephenCrump]

Or in one line:

Sheet3.Range("A2:A" & ALastRow).Formula = "=LEFT(TRIM(I2),3) & MID(TRIM(I2),5,4) & RIGHT(TRIM(I2),1)"

 

[Fluff]

Another option

Sheet3.Range("A2:A" & ALastRow).FormulaR1C1 = "=LEFT(TRIM(RC[8]),3) & MID(TRIM(RC[8]),5,4) & RIGHT(TRIM(RC[8]),1)"

 

[mellymelle]

Try: Sheet3.Range("A2").AutoFill Destination:=Sheet3.Range("A2:A" & ALastRow), Type:=xlFillDefault

(Without this qualification, your code will give a run-time error 1004 if Sheet3 is not the Active Sheet)

Thank you Stephen, that completely explains it. And thanks for your suggestions - it must be completely obvious that I'm super green at this!