AutoFill Formula Based On Data In Another Sheet

Here is my test example I am trying to figure this out with.


Sub a()
Dim rg As Range
'set formulas to be filled
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF('sheet2'!RC="""","""",'sheet2'!RC)"
Selection.AutoFill Destination:=Range("A1:L1"), Type:=xlFillDefault
'now get autofill to populate until no more data on sheet 2
Set rg = [a2]
Set rg = Range(rg, Cells(Rows.Count, rg.Column).End(xlUp))
rg.Cells(2, 2).AutoFill Destination:=rg, Type:=xlFillDefault
End Sub

Without VBA, once I set the formulas for A1:L1, I can use the mouse to autofill all columns until Im around row 27,000.

Id like to be able to run a check for the end of data in sheet 2 so that if the sheet2 changes, it will all be included.

The resulting sheet1 cannot have any null values, so a perfect end of data check is necessary.