Use VBA to Autofill a Row until the end of the number of data in another row

How do you lookup then copy and paste data when there's previous data from the above cell. My file is being updated daily so there's new data. I need to make a VBA that looks up the new data then copy and paste it as values. 

1. I need to look up the last entry on Column C on Sheet1 then enter the data on Column B (IFERROR(INDEX(Sheet2!B:B,MATCH(Sheet1!B:B,Sheet2!A:A,0)),"") on Sheet2 

2. Autofill data until the last row

3. Copy and paste as values

4. Run again once there's new data on 

I don't know what code to use on the autofill part. 

@Haytham

I apologize that I am not able to find what I need among so much work toward this subject...please help.

I have a workbook that has 4 sheets, all tables. I need to be able to enter a new line of information in the first table (a non-formulated table) and then have all the other tables drag down formulas to match the number of data rows in the first table... How do I get this to do this automatically? What would be the code on the button to get this to happen? Any help would be appreciated!!

' version 1

Sub AutoFill_lengthy_self_explanatory()

    Dim startcell, endcell As Range

    Set startcell = Selection.Range("A1")

    Set endcell = startcell.Offset(0, -1).End(xlDown).Offset(0, Selection.Columns.Count())

    Selection.AutoFill Destination:=Range(startcell, endcell)

End Sub

' version 2: same but one-liner that you can use instead of a function:

Selection.AutoFill Destination:=Range(Selection.Range("A1"), Selection.Range("A1").Offset(0, -1).End(xlDown).Offset(0, Selection.Columns.Count()))

Kai El Harrar 

Hi

If you wee to create a Table instead, Insert > Table, then your formula would automatically be inserted as you add rows to the Table, without the need for any VBA code.

Replace these lines of code:

Selection.AutoFill Destination:=Range("AY2:AY1662")
Range("AY2:AY1662").Select

With this:

Selection.AutoFill Destination:=Range("AY2:AY" & Range("E" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select

Give it a try!

And provide me with any feedback!