Help duplicating from one cell onto multiple rows

Hi, first post here, hope i have done the process correctly....

I have been trying to figure out the best way to do this for a few days but keep getting stumped.

I have a data set from a sports competition whereby each row specifies all data from one match (date, day, time, venue, home team, away team etc). The end result i want to get to is to be able to call/filter for how many days rest a team has had between games. To get to this i have decided to split each game to two rows - row one for home team with all info and row two for away team with all info. This way i am able to create a formula to count days between dates since team last appeared in the "team" column.

The problem i am having now is trying to create a macro which enables me to loop through the data set and print the information that pertains to both teams onto consecutive rows. For example - the date value in "2022 data" cell A2 should print on "2022 format" cells A2 (home) and A3 (away). Then the date value in A3 should print on new sheet in cells A4 and A5 etc etc. I have managed to get it to work but only for one iteration - it then overwrites itself as i dont know how to make it print from the next empty row. The order in which everything happens is obviously not important, just that the information ends up in the right spot.

Hope someone can help! Thanks a bunch - pics and code below

Attachment 30381

Code:

---

Sub formatdata()



'Define Start and End Lines for our For Loop
    start_line = 2
    end_line = Sheets("2022 data").Cells(Rows.Count, "A").End(xlUp).Row
   
'clear contents


    Sheets("2022 format").Range("A2:M203").ClearContents
   
'define line to start printing on format sheet
    new_line = 2


'For loop to run through 2022 data
    For start_line = 2 To 3
   
    'define variables on 2022 data sheet
    home_date = Seets("2022 data").Cells(start_line, 1).Value
    away_date = Sheets("2022 data").Cells(start_line, 1).Value
    home_day = Sheets("2022 data").Cells(start_line, 2).Value
    away_day = Sheets("2022 data").Cells(start_line, 2).Value
    home_time = Sheets("2022 data").Cells(start_line, 3).Value
    away_time = Sheets("2022 data").Cells(start_line, 3).Value
    home_team = Sheets("2022 data").Cells(start_line, 4).Value
    away_team = Sheets("2022 data").Cells(start_line, 5).Value
    venue = Sheets("2022 data").Cells(start_line, 6).Value
    home_score = Sheets("2022 data").Cells(start_line, 7).Value
    away_score = Sheets("2022 data").Cells(start_line, 8).Value
   
   
   
    Sheets("2022 format").Cells(start_line, 1).Value = home_date
    Sheets("2022 format").Cells(start_line, 2).Value = home_day
    Sheets("2022 format").Cells(start_line, 3).Value = home_time
    Sheets("2022 format").Cells(start_line, 5).Value = home_team
    Sheets("2022 format").Cells(start_line, 6).Value = venue
    Sheets("2022 format").Cells(start_line + 1, 1).Value = away_date
    Sheets("2022 format").Cells(start_line + 1, 2).Value = away_day
    Sheets("2022 format").Cells(start_line + 1, 3).Value = away_time
    Sheets("2022 format").Cells(start_line + 1, 5).Value = away_team
    Sheets("2022 format").Cells(start_line + 1, 6).Value = venue
   
     
   
    Next start_line


 
   
End Sub

---

This may help, untested code:

Code:

---

Sub formatdata()
    Dim start_line As Long
    Dim wsData As Worksheet
    Dim wsFormat As Worksheet
    Dim nr As Long
   
    Set wsData = Sheets("2022 data")
    Set wsFormat = Sheets("2022 format")
    wsFormat.Range("A2:M" & wsFormat.Range("A" & Rows.Count).End(xlUp).Row).ClearContents
   
    For start_line = 2 To wsData.Range("A" & Rows.Count).End(xlUp).Row
        With wsFormat
            nr = .Range("A" & Rows.Count).End(xlUp).Row + 1
            ' home
            .Cells(nr, 1).Value = wsData.Cells(start_line, 1).Value
            .Cells(nr, 2).Value = wsData.Cells(start_line, 2).Value
            .Cells(nr, 3).Value = wsData.Cells(start_line, 3).Value
            .Cells(nr, 5).Value = wsData.Cells(start_line, 4).Value
            .Cells(nr, 6).Value = wsData.Cells(start_line, 6).Value
            .Cells(nr, 7).Value = wsData.Cells(start_line, 7).Value
            ' away
            .Cells(nr + 1, 1).Value = wsData.Cells(start_line, 1).Value
            .Cells(nr + 1, 2).Value = wsData.Cells(start_line, 2).Value
            .Cells(nr + 1, 3).Value = wsData.Cells(start_line, 3).Value
            .Cells(nr + 1, 5).Value = wsData.Cells(start_line, 5).Value
            .Cells(nr + 1, 6).Value = wsData.Cells(start_line, 6).Value
            .Cells(nr + 1, 7).Value = wsData.Cells(start_line, 8).Value
        End With
    Next start_line
End Sub

---