Can VBA dynamically insert data in rows on one worksheet based on the number rows w. data on another

Kenneth Green 

Try this:

Sub PullTitlesDataTest2()
    Dim LastRow As Long

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
        .EnableAnimations = False
    End With

    LastRow = Worksheets("Track Data").Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    '   Starting with cell A2 through to cell I2, and down to row 40,000, this inserts the forumlas to
    '   pull the data from the worksheet "Track Data*

    Range("A2:A" & LastRow).FormulaR1C1 = "=IF(ISBLANK('Track Data'!RC),"""",'Track Data'!RC)"
    Range("B2:B" & LastRow).FormulaR1C1 = "=IF(ISBLANK('Track Data'!RC[-1]),"""",'Track Data'!RC)"
    Range("C2:C" & LastRow).FormulaR1C1 = "=IF(ISBLANK('Track Data'!RC[-2]),"""",'Track Data'!RC[6])"
    Range("D2:D" & LastRow).FormulaR1C1 = "=IF(ISBLANK('Track Data'!RC[-3]),"""",'Track Data'!RC[6])"
    Range("E2:E" & LastRow).FormulaR1C1 = "=IF(ISBLANK('Track Data'!RC[-4]),"""",'Track Data'!RC)"
    Range("F2:F" & LastRow).FormulaR1C1 = "=IF(AND(RC[-5]=R[1]C[-5],RC[-4]=R[1]C[-4],RC[-3]=R[1]C[-3],RC[-2]=R[1]C[-2]),"""",TEXTJOIN(""+"",,CHOOSE({1,2},IF(COUNTIFS(R2C[-1]:RC[-1],1,R2C[-4]:RC[-4],RC[-4]),""M"",""""),IF(COUNTIFS(R2C[-1]:RC[-1],2,R2C[-4]:RC[-4],RC[-4]),""S"",""""),2)))"
    Range("G2:G" & LastRow).FormulaR1C1 = "=IF(ISBLANK('Track Data'!RC[-6]),"""",'Track Data'!RC[-1])"
    Range("H2:H" & LastRow).FormulaR1C1 = "=IF(ISBLANK('Track Data'!RC[-7]),"""",'Track Data'!RC[-1])"
    Range("I2:I" & LastRow).FormulaR1C1 = "=IF(ISBLANK('Track Data'!RC[-8]),"""",IF('Track Data'!RC[-1]="".wav"",""Wav"",IF('Track Data'!RC[-1]="".flac"",""Flac"",IF('Track Data'!RC[-1]="".aif"",""Aif"",IF('Track Data'!RC[-1]="".mp3"",""MP3"",IF('Track Data'!RC[-1]="".SD2"",""SD2"",""UNKNOWN TYPE""))))))"

    Application.GoTo Reference:="R2C1"

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
        .EnableAnimations = True
    End With

End Sub