excel - Error 1004 AutoFill

Question

The error is being thrown on the line

ThisWorkbook.Sheets("data").Range("AJ2:AM2").AutoFill Destination:=Range("AJ2:AM" & localLastRow)

It was previously cooperating, but after I corrected another error it seems that it doesn't want to play nice anymore. The source is contained within the destination. I am just not sure where the problem is coming from.

Any help would be very appreciated. I have posted the entire macro below. It will eventually be one that is called into a main macro.

Sub FormulaUpdate()
'
' FormulaUpdate Macro
' Updates Columns AJ through AS
'
Dim localLastRow As Long
Dim sourceLastRow As Long
Dim wbName As String
Dim wbPath As String
Dim sourceSheet As Worksheet
Dim sourceRange As Range
Dim thisSheet As Worksheet

Application.ScreenUpdating = False

'sets strings from user's selection of Item Branch Report
wbPath = GetFile("Select Item Branch Report to be Used")
wbName = GetFilenameFromPath(wbPath)

Workbooks.Open(wbPath, ReadOnly:=True).Activate

'sets workseets to be referenced
Set sourceSheet = ActiveWorkbook.Sheets(1)
Set thisSheet = ThisWorkbook.Sheets("data")

'counts rows in selected item branch report for use elsewhere in macro
sourceLastRow = sourceSheet.Range("A" & Rows.Count).End(xlUp).Row

'range for use in vlookup formula, for both system leadtime and order min columns
Set sourceRange = sourceSheet.Range("B1:BG" & sourceLastRow)

'Counts rows in this workbook for use elswhere in macro
localLastRow = thisSheet.Range("A" & Rows.Count).End(xlUp).Row

'uses formulas in cells to autofill the data
thisSheet.Range("AJ2:AM2").AutoFill Destination:=thisSheet.Range("AJ2:AM" & localLastRow)


'loops through each row of both the system lead time, and the order min column, and sets the value from item branch report
For i = 2 To localLastRow

thisSheet.Range("AN" & i).Value = Application.WorksheetFunction.VLookup(thisSheet.Range("C" & i), sourceRange, 53, False)
thisSheet.Range("AP" & i).Value = Application.WorksheetFunction.VLookup(thisSheet.Range("C" & i), sourceRange, 58, False)
Application.StatusBar = "Referencing IBR: " & i & " of " & localLastRow & ": " & Format(i / localLastRow, "0%")

Next i

'uses formulas in cells to autofill the data
thisSheet.Range("AO2").AutoFill Destination:=thisSheet.Range("AO2:AO" & localLastRow)
thisSheet.Range("AQ2:AS2").AutoFill Destination:=thisSheet.Range("AQ2:AS" & localLastRow)

Workbooks(wbName).Close (False)

Answer 1

I mention above that is the wrong solution. Read this for background on why relying on Select and Activate methods is usually problematic, and should be avoided at all times. You have already encountered one frustrating problem -- which is that you need to keep track of which sheet is "active" and constantly update the code to have the appropriate sheet "active". This makes for sloppy code that is difficult to navigate, and more expensive to execute.

The appropriate solution would be to fully qualify your range, for example:

ThisWorkbook.Sheets("data").Range("AJ2:AM2").AutoFill Destination:=ThisWorkbook.Sheets("data").Range("AJ2:AM" & localLastRow)

Why?

Because, as you observe, an unqualified range always refers to the ActiveSheet. One solution (the wrong one) is to continuously make the right sheet Active. The right solution is to fully qualify your ranges, especially when working across multiple workbooks or worksheets.