AutoFill Formula Issue in Table

[crobinson661]

Good Morning,

I have a macro that I am using to AutoFIll a VLOOKUP formula to the bottom of a table.

It keeps filling past the bottom of the table and creating new, expanding the table and creating blank rows, except the formula column of course has #N/A.

The issue is I am then converting this to a pivot and it picks up the blanks as a data point. :-/

I'm not sure how to prevent this. I'm familiar with how to write an autofill to max data range when a table is NOT included, but I don't see the same code and I anticipate it's because I'm using a table.

Range("E1").Select
    ActiveCell.FormulaR1C1 = "Formula"
    Range("E2").Select
       Application.WindowState = xlMaximized
    Application.CutCopyMode = False
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP([@[Column D]],'[ReferenceFile.xlsx]Source Data - Per Item Pricing'!C1:C2,2,0)"

Any help would be appreciated.

 

[mrshl9898]

I can't seem to replicate this issue, could you upload the file somewhere and share a link? Maybe check the Pivot Tables Source?

This will work if not in a table.

Sub InsertLookup()

Dim sht As Worksheet
Dim LastRow As Long

Set sht = ActiveSheet

LastRow = sht.Cells(sht.Rows.Count, "D").End(xlUp).Row

Range("E1") = "Formula"
Range("E2").FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[ReferenceFile.xlsx]Source Data - Per Item Pricing'!C1:C2,2,0)"
        
Range("E2").Copy Range("E2:E" & LastRow)
        

End Sub