FillDown method failing in Excel 2010

Question

I'm getting an error message related to the below VBA Macro in Excel 2010.

Purpose of the Macro:

I have a Table in an Excel worksheet that is refreshed via an SQL query. the table contains 1 week per column of data, as well as, a few columns containing attributes preceding the date columns.

After the table is refreshed each week the Macro should do the following:

1. Find the last column in the table in row 2 and label that cell "Over Capacity %"

2. Find last row of table (rows are variable and may change every time data is refreshed)

3. Copy & fill the formula in row 3 of the last column all the way down to the last row of the last column of the table.

4. Format the numbers in the last column as percentages with 2 decimal places.

5. sort the entire table based on the percentages in the last column.

Where the Error Occurs

The error occurs in what is listed as step 3 above. I have attempted the both the AutoFill and FillDown methods.

Related line of VBA is:

Worksheets("Heatmap - FTE").Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).FillDown

Error Message Shown

Run-time error '1004': Application-defined or object-defined error

Full VBA code for the Macro:

Sub Heatmap_FTE_Update()
Dim LastCol As Long
Dim LastRow As Long
Dim rng As Range
Dim DateValue As String
    
Set rng = Sheets("Heatmap - FTE").Range("B2:BZ2")

LastCol = Worksheets("Heatmap - FTE").Cells(2, Columns.Count).End(xlToLeft).Column
    
'MsgBox LastCol

rng.Parent.Cells(2, LastCol + 1).Value = "% Over Capacity"

DateValue = rng.Parent.Cells(2, LastCol).Value

Worksheets("Heatmap - FTE").Cells(3, LastCol + 1).Formula = "=IFERROR(COUNTIF(Table_Query_from_MS_Access_Database[@[1/1/2016]:[" & DateValue & "]],"">""&40)/(COUNT(Table_Query_from_MS_Access_Database[@[1/1/2016]:[" & DateValue & "]])),0)"

LastRow = Worksheets("Heatmap - FTE").Cells(Rows.Count, "B").End(xlUp).Row

'MsgBox LastRow

Worksheets("Heatmap - FTE").Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).FillDown

Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).NumberFormat = "0.00%"

Range(Cells(3, 2), Cells(LastRow, LastCol + 1)).Sort key1:=Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)), order1:=xlDescending, Header:=xlYes


End Sub

Any input is appreciated. Thank you.

Answer 1

Found a solution.

Apparently, I didn't need to reference the worksheet for the FillDown method. Noticed that macro worked if I was on the worksheet where it was intended to run, but failed at the FillDown method if I was on another worksheet.

Resolved that by selecting the worksheet.

Working code is:

Sub Heatmap_FTE_Update()
Dim LastCol As Long
Dim LastRow As Long
Dim rng As Range
Dim DateValue As String

Sheets("Heatmap - FTE").Select

Set rng = Sheets("Heatmap - FTE").Range("B2:BZ2")

LastCol = Worksheets("Heatmap - FTE").Cells(2, Columns.Count).End(xlToLeft).Column

'MsgBox LastCol

rng.Parent.Cells(2, LastCol + 1).Value = "% Over Capacity"

DateValue = rng.Parent.Cells(2, LastCol).Value

Worksheets("Heatmap - FTE").Cells(3, LastCol + 1).Formula = "=IFERROR(COUNTIF(Table_Query_from_MS_Access_Database[@[1/1/2016]:[" & DateValue & "]],"">""&40)/(COUNT(Table_Query_from_MS_Access_Database[@[1/1/2016]:[" & DateValue & "]])),0)"

LastRow = Worksheets("Heatmap - FTE").Cells(Rows.Count, "B").End(xlUp).Row

'MsgBox LastRow

Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).FillDown

Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)).NumberFormat = "0.00%"

Range(Cells(3, 2), Cells(LastRow, LastCol + 1)).Sort key1:=Range(Cells(3, LastCol + 1), Cells(LastRow, LastCol + 1)), order1:=xlDescending, Header:=xlYes

Sheets("Control").Select

End Sub

However, if you have a more elegant solution or explanation of my error that is appreciated.

Thanks.