VBA Compile Error occurring

Question

The code below is meant to run when the Workbook is first opened.

Sub Auto_Open()

Dim LastRow As Integer
LastRow = Sheet6.UsedRange.Rows.Count

ActiveWorkbook.RefreshAll

Sheet6.AutoFill Destination:=Range("Y2:Y" & LastRow)

End Sub

It automatically runs a Refresh All to update any queries or formula in the WorkBook and then autofills the list of data in column Y of sheet6 to the last row of data that can be found in the WorkSheet.

When I go to run the code I get a 'Compile Error: Method of data member not found' which highlights.

.Autofill

What I don't understand is that this works perfectly well on an identical spreadsheet, not just this one.

I have also tried the following code which doesn't work on this sheet but does on the other.

Sub Auto_Open()

ActiveWorkbook.RefreshAll

Sheet6.AutoFill_ListSource

End Sub

ListSource is the name of the table in column Y that I am trying to autofill.

Use Excel Help and you'll see that Autofill requires a range, not a sheet (and the destination must include this range). — SJR, Nov 29 '17 at 14:41

Shai Rado · Accepted Answer · 2017-11-29T15:04:59.970

3

Change:

Sheet6.AutoFill Destination:=Range("Y2:Y" & LastRow)

to:

Sheet6.Range("Y2").AutoFill Destination:=Sheet6.Range("Y2:Y" & LastRow)

Note: a "safer" way to get the last row, will be using the Find function:

Dim LastCell As Range
Dim LastRow As Long

With Sheet6
    Set LastCell = .Cells.Find(What:="*", After:=.Cells(1), Lookat:=xlPart, LookIn:=xlFormulas, _
                        searchorder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=False)
    If Not LastCell Is Nothing Then
        LastRow = LastCell.Row
    Else
        MsgBox "Error! worksheet is empty", vbCritical
        Exit Sub
    End If
End With

edited Nov 29 '17 at 15:04

answered Nov 29 '17 at 14:40

Shai Rado

33,032
6
29
51

1

++ Long time ago Si put [THIS](http://www.vbforums.com/showthread.php?511766-Classic-VB-Why-is-using-the-End-statement-(or-VB-s-quot-stop-quot-button)-a-bad-idea) alltogether. Interesting read. BTW if the user is looking for last row in a single column then `.Find` would be an overkill. `xlUp` will solve the problem... Also check out the use `Application.WorksheetFunction.CountA(.Cells)` in [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920). Just my 2 cents :) – Siddharth Rout Nov 29 '17 at 15:10
Thanks for the help - it works a treat! Although, I am still confused as to why my original code was not working on this spreadsheet but was on another... – Dan M Nov 29 '17 at 15:18
@DanM because you didn't qualify `Range("Y2:Y" & LastRow)` with `Sheet6`. – Shai Rado Nov 29 '17 at 15:28

VBA Compile Error occurring

1 Answers1