Using VBA to achieve looping a user input form and consolidate the output into another worksheet

[RyanKJM]

Hello,

I have a user input interface in Excel that looks like this:

Where cell B1, B2 are user input cells; A4 and below are also user input cells, but the number of rows is not limited, so the user can enter 3 accounts as shown in screenshot example, or more than that;

I am looking to add a Macro where when the user clicks Submit, the results are consolidated into another worksheet with a format like this:

The display of the data entered under Account is broken down into different rows, while the first and last name entry is multiplied;

With VBA, I am able to achieve the basic function of submitting with one entry of data under Account, using this:

Private Sub data_input()
ws_output = "Sheet2"
next_row = Sheets(ws_output).Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
Sheets(ws_output).Cells(next_row, 1).Value = Range("FN").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("LN").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("A_1").Value
End Sub

(The FN, LN, A_1 are the names I assigned to the user input cells)

However unless I copy and paste the Sheets(ws_output).Cells(next_row, 1) lines multiple times, it does not achieve for multiple entries of data under Account, nor does it detect by itself how many rows of data the user enters under Account;

What would be some of the ways to tweet the VBA code to achieve this;

Many thanks,

 

[Artik]

e.g.

Private Sub data_input()
    Dim ws_output As Worksheet
    Dim next_row As Long
    Dim i As Long

    Set ws_output = Worksheets("Sheet2")
    next_row = ws_output.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

    Do Until IsEmpty(Range("A_1").Offset(i))
        ws_output.Cells(next_row, 1).Value = Range("FN").Value
        ws_output.Cells(next_row, 2).Value = Range("LN").Value
        ws_output.Cells(next_row, 3).Value = Range("A_1").Offset(i).Value
        
        next_row = next_row + 1
        i = i + 1
    Loop
    
End Sub

Artik