A monthly scorecard with an annual total, added to on a monthly basis

[chandelirious]

Hi everyone, I'm struggling with a problem that I know is doable, but I can't get my head around it.

So I've got an Excel spreadsheet with what I'm going to say is 80 footballers on it. (They're not, but it fits the purpose and keeps the data clean!)

Let's just say that I need to keep a record of how many games each footballer plays, how many goals they score, goals they save, yellow and red cards they get. We're doing this for the month of September.

I gather all of this data at the end of the month.

I input it into Sheet 1.

Due to some fantastic VBA wizardry, each player also has their own sheet - so Player 1 has a sheet called Player 1, Player 2's sheet is called Player 2... etc.

I want to press a magic button, have all of the data for each player that is currently recorded in Sheet 1 to be pasted into each player's individual sheet. Each player's sheet has the same headings but in cells C2 - C13, we have September - August. So the data from September will go in cells D2:H2

HOWEVER.

I then need to wipe Sheet 1, so that it's blank, ready for the next month's data. And then when I have all of October's data, I want to press the magic button again, but this time, the data will transpose into cells D3:H3.

How do I do that part? So basically, by August 2022, each player will have stats in for each month, which shows everything that they've done over the past 12 months.

Thanks so much for your help!

Name	Type	Month	Games Played	Goals Scored	Goals Saved	Yellow Cards	Red Cards
Player 1	Striker	September	9​	3​		1​
Player 2	Midfielder	September	9​				2​
Player 3	Midfielder	September	10​
Player 4	Midfielder	September	9​
Player 5	Striker	September	7​	3​
Player 6	Goalkeeper	September	8​		1​
Player 7	Goalkeeper	September	1​		3​		1​
Player 8	Defender	September	6​			1​
Player 9	Defender	September	5​			5​
Player 10	Defender	September	11​

 

[SQUIDD]

Hi

I believe the below should do what you require.

However, if you repedidly click the button, it will keep firing the data over to the next available rows on your player sheets.

So i would suggest you need to trap it 1st, something like checking to see if the month has already been run, assume sheet1.cell c3 will always contain the current month being transferred?

see how you get on with the below and let me know if you need the catch for the possible data duplication if pressed more than once.
what range to you want to clear on sheet1 at the end?

And at the end, should the next month be added ready?

Sub TRANSFER_ME()
Application.ScreenUpdating = False
For A = 2 To Range("'SHEET1'!A" & Rows.Count).End(xlUp).Row
    MY_Sheet = Range("'SHEET1'!A" & A)
    LRB = Range("'" & MY_Sheet & "'!A" & Rows.Count).End(xlUp).Row + 1
    Range("'SHEET1'!A" & A & ":H" & A).Copy
    Range("'" & MY_Sheet & "'!A" & LRB).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next A
Application.ScreenUpdating = True
End Sub