Compile error when trying to autofill with VBA

Saab95

New Member
Joined
Mar 26, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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 :cool:
 
Last edited by a moderator:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Mar 26, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Please use code tags when posting code.

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

VBA Code:
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

New Member
Joined
Mar 26, 2021
Messages
26
Office Version
  1. 365
Platform
  1. Windows
Please use code tags when posting code.

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

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,332
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It works for me. You know it's only filling AT2 across the sheets?
 

Forum statistics

Threads
1,140,933
Messages
5,703,239
Members
421,287
Latest member
Ravi bhatia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top