Using VBA to copy data to new sheet and find data from other sheets

[mattadams84]

Hello,

I am trying to create a workbook that creates a list of of football match fixtures with the prematch stats of the match.

I have an API that successfully pulls in data (the statistics) across multiple different sheets (1 sheet per league). The league statistics sheet has the teams in the rows and then many columns which contain statistics for the team's home matches, away matches, and a combination of the both (overall).

Each league has a unique numerical ID and each team has a unique numerical ID.

I then have another sheet that pulls in via the API a list of the todays fixtures. What l would like to achieve is to dynamically/automatically create a whole new sheet that has the list of the fixtures but also shows the correlating stats of each team. So I guess by using the ID's of the teams from the "todays fixture" sheet is searches through the other sheets, finds the relevant data and then generates a new sheet with all the fixtures and stats. Obviously it would need to work out who is playing at home and who away so it finds the relevant stats.

Is this possible in excel and VBA? For info i know very little about VBA, i can try and decipher the code so i sort of understand how it works, but apart from that i have fairly limited knowledge. Happy to share the sheet if anyobdy is interested in helping me out !

Regards

[Aussiebear]

I believe that this should be a database project rather than Excel. Should you not get any response here shortly I will move this thread to Access forum.

[Paul_Hossler]

Attach a sample workbook with enough data to show the inputs and an example of what you want for an output

[mattadams84]

I have attached a file to this thread. Many thanks for the help

[Jesus_Loves]

Perhaps experimenting with the .Find method will shed insight?


Sheets(1).Range("A:A").Find("numerical ID").Select ' find the "numerical ID" and select it

[LucyHills]

Yes, what you're describing is certainly possible with Excel and VBA (Visual Basic for Applications). VBA allows you to automate tasks in Excel by writing custom code. In your case, you can use VBA to loop through the data, match the team IDs, and generate a new sheet with the relevant statistics for each fixture.

Here's a simplified example to get you started. This assumes you have a sheet named "Fixtures" for your fixture data:


Sub GenerateFixtureStats()
Dim fixtureSheet As Worksheet
Dim leagueSheet As Worksheet
Dim newSheet As Worksheet
Dim homeTeamID, awayTeamID As Long
Dim lastRow As Long
Dim i As Long
' Set the worksheets
Set fixtureSheet = Worksheets("Fixtures")
Set newSheet = Worksheets.Add
' Loop through fixtures
lastRow = fixtureSheet.Cells(fixtureSheet.Rows.Count, "A").End(xlUp).Row
For i = 2 To lastRow ' Assuming data starts from row 2
' Get home and away team IDs
homeTeamID = fixtureSheet.Cells(i, 1).Value
awayTeamID = fixtureSheet.Cells(i, 2).Value
' Find the corresponding data in the league sheets and copy to the new sheet
CopyTeamStats homeTeamID, "PremierLeague", newSheet, i, 2 ' Replace "PremierLeague" with your actual league sheet name
CopyTeamStats awayTeamID, "PremierLeague", newSheet, i, 3 ' Same here, replace "PremierLeague"
' Move to the next row in the new sheet
newSheet.Cells(i, 4).Value = "vs" ' Just a separator for clarity
Next i
End Sub

Sub CopyTeamStats(teamID As Long, leagueSheetName As String, newSheet As Worksheet, newRow As Long, newCol As Long)
' This subroutine copies the team stats to the new sheet
Dim leagueSheet As Worksheet
Dim teamRow As Range
' Set the league sheet
Set leagueSheet = Worksheets(leagueSheetName)
' Find the team row in the league sheet
Set teamRow = leagueSheet.Columns(1).Find(teamID, LookIn:=xlValues)
If Not teamRow Is Nothing Then
' Copy the relevant stats to the new sheet
leagueSheet.Rows(teamRow.Row).Copy newSheet.Cells(newRow, newCol)
Else
' Handle the case where the team ID is not found
newSheet.Cells(newRow, newCol).Value = "Team ID not found"
End If
End Sub


This code is a basic example and may need adjustments based on your actual crm data enrichment (https://www.globaldatabase.com/what-is-lead-enrichment). Make sure to replace "PremierLeague" with the actual name of your league sheet.