Compile error when trying to autofill with VBA

[Saab95]

Hi,

I have a vba that insert a formula in cell AT2 across all sheets. So far, so good.

Now I have tried to autofill this formula across all sheets to the last column. But end up with a 'compile error: Method or data member not found'.

vba is the below:

Sub Sheet_Date2()
With ThisWorkbook.Worksheets
    Dim lastRow As Long
    lastRow = .Item(1).Range("AR" & Rows.Count).End(xlUp).Row
    .Range("AT2").AutoFill Destination:=.Range("AT2:AT" & lastRow)
    .FillAcrossSheets.Item(1).Range ("AT2"), xlFillWithAll
    End With
End Sub

Any idea. Thks

 

[RoryA]

Your With ThisWorkbook.Worksheets should be With ThisWorkbook.Worksheets(1) and then remove the .Item(1) part from the lastrow line. The Worksheets collection doesn't have a Range property.

 

[Saab95]

Hi,

So changed to:

Sub Sheet_Date2()
With ThisWorkbook.Worksheets(1)
Dim lastRow As Long
lastRow = .Range("AR" & Rows.Count).End(xlUp).Row
.Range("AT2").AutoFill Destination:=.Range("AT2:AT" & lastRow)
.FillAcrossSheets.Range ("AT2"), xlFillWithAll
End With
End Sub

but now have a 'runtime error 438: Object doesn't support this property or method'

 

[RoryA]

Please use code tags when posting code.

My fault - I didn't see the FillAcrossSheets bit. I'd suggest:

Sub Sheet_Date2()
   With ThisWorkbook.Worksheets
      Dim ws as worksheet
      set ws = .Item(1)
      Dim lastRow As Long
      lastRow = ws.Range("AR" & Rows.Count).End(xlUp).Row
      ws.Range("AT2").AutoFill Destination:=ws.Range("AT2:AT" & lastRow)
      .FillAcrossSheets ws.Range ("AT2"), xlFillWithAll
   End With
End Sub

 

[Saab95]

Please use code tags when posting code.

My fault - I didn't see the FillAcrossSheets bit. I'd suggest:

Sub Sheet_Date2()
   With ThisWorkbook.Worksheets
      Dim ws as worksheet
      set ws = .Item(1)
      Dim lastRow As Long
      lastRow = ws.Range("AR" & Rows.Count).End(xlUp).Row
      ws.Range("AT2").AutoFill Destination:=ws.Range("AT2:AT" & lastRow)
      .FillAcrossSheets ws.Range ("AT2"), xlFillWithAll
   End With
End Sub

Thks.

For some reasons it applies it only on sheet 1 and not on all the other sheets.

 

[RoryA]

It works for me. You know it's only filling AT2 across the sheets?