how to insert rows with vba and apply formulas in excel?

[excel12345u]

I'm not good at VBA but I'm working on this project and I need to add VBA. I created a Minimal reproducible problem just like my real problem. I have different sections, in this case, books, books have chapters, and the number of chapters is changing. I want the VBA adds rows to the section if it has more chapters and to apply formulas from above.

I have made formulas to list chapters but either I add more rows or I need more rows. As you can see in my example below, in column b you can see how many rows I need.

Please find my Excel file with a reproducible example on the following link.

Thanks in advance, any help is more than welcome!

 

[excel_learnerz]

1. Press Alt + F11 to open the VBA editor.
2. Click on "Insert" > "Module" to insert a new module.
3. Paste the following code into the module:

Sub InsertRowsAndApplyFormulas()
Dim ws As Worksheet
Dim i As Long, lastRow As Long
Dim numRows As Long
Dim rng As Range

Set ws = ThisWorkbook.Worksheets("Sheet1") 'Replace "Sheet1" with the name of your worksheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False

For i = lastRow To 2 Step -1
numRows = ws.Cells(i, "B").Value
If numRows > 1 Then
ws.Rows(i + 1 & ":" & i + numRows - 1).Insert Shift:=xlDown
Set rng = ws.Range(ws.Cells(i, "A"), ws.Cells(i + numRows - 1, "A"))
rng.FillDown
Set rng = ws.Range(ws.Cells(i, "C"), ws.Cells(i + numRows - 1, "C"))
rng.FillDown
End If
Next i

Application.ScreenUpdating = True
End Sub

1. Replace "Sheet1" with the name of your worksheet that contains the data.
2. Close the VBA editor.
3. Press Alt + F8 to open the "Macro" dialog box, select InsertRowsAndApplyFormulas, and click "Run".