Help duplicating from one cell onto multiple rows

[tuckerboxen]

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

30381


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

[georgiboy]

This may help, untested 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