For Each ws autofill

[Karen53]

Hi,

I have tried various combinations to try and get this to work to no avail.
It is only to fun on those worksheets between the 'First' worksheet and
'Last" worksheet. What is wrong with my code? I'm getting a 'Select method
of range class failed'.

Sub AutofillFormula()

Dim ws As Worksheet
Dim FirstIndex As Long
Dim LastIndex As Long

FirstIndex = Firstpg.Index
LastIndex = Lastpg.Index

For Each ws In ThisWorkbook.Worksheets

If ws.Index > FirstIndex Then
If ws.Index < LastIndex Then

ws.Range("N49").Select
Selection.AutoFill Destination:=Range("N49:N349"),
Type:=xlFillDefault
ws.Range("N49:N349").Select

End If
End If
Next ws

End Sub

 

[Bernie Deitrick]

Karen,

You cannot select a range on a worksheet that is not active.

ws.Select
ws.Range("N49").Select
Selection.AutoFill Destination:=ws.Range("N49:N349"), _
Type:=xlFillDefault
ws.Range("N49:N349").Select

HTH,
Bernie
MS Excel MVP