Auto Fill formulas for some columns

Hi There,

Need help for resolving the below situation:

Every time after a new batch extract I have to copy column to O from the new batch extact file at the last blank row and fill the formulas from A to H along with the new batch number.

I have a macro which copy the new batch extract from new batch extract file at the firct blank row from column I through to last column. And it works perfectly fine.

What I need is to update the macro to populate the formuals once I add any data for a new batch.

I have attached a sample file to make things clearly understand what I am explaining. In
the file 19th row to 24th row refers the new batch which I want to populate the formulas with for column A to H. I have copied the Column I to O fron the new batch extract say 6.

Any ideas how can I say to fill these columns with formulas until last row upto which
data is available.

Any help and advice is very appreciated.

My Code is below :

Private Sub CommandButton1_Click()

Dim ws As Worksheet
Dim i As Long, j As Long, lrow As Long

For j = 1 To 4

    With Sheets(j)
        If .FilterMode Then .ShowAllData
       .Cells.EntireRow.Hidden = False
       .Cells.EntireColumn.Hidden = False
    End With
Next j    

For i = 1 To 2
    Set ws = Workbooks("Batch_12_Report.xls").Worksheets(i)
    lrow = ws.Range("A" & Rows.Count).End(xlUp).Row
    ws.Range("A2:CZ2" & lrow).Copy ThisWorkbook.Worksheets(i).Range("K" & Rows.Count).End(xlUp).Offset(1, 0)
Next i

For i = 3 To 4
    Set ws = Workbooks("Batch_12_Report.xls").Worksheets(i)
    lrow = ws.Range("A" & Rows.Count).End(xlUp).Row
    ws.Range("A2:BG2" & lrow).Copy ThisWorkbook.Worksheets(i).Range("L" & Rows.Count).End(xlUp).Offset(1, 0)
Next i
        
End Sub

Hi John,

That works amazingly. Thank you very much. Much appreciate.

Thanks,
Su.