Macro: Dynamic creation of sheet based on cell value then copy range to the new sheet

[BeachRock]

I've created a workbook which tracks stats for my local pool league. There are team, partner sets and player stats collected per week. I also want to track player stats on a per game basis and this is the reason for the post. I've run into a problem in that I can't copy the range of collected data for each player per game played to just one sheet for all players due to the number of rows that would be generated for the maximum players that could be involved in the league (144), the maximum number of weeks (48) and maximum number of games per player per season (240). This combined with the number of INDEX array formulas that would collect this data per player on another sheet makes the collection for each individual player run very slow for each entry. I've determined that I need to separate data on separate sheets per the week being collected. This would be a total of 48 sheets. This way, rows per sheet to be indexed will be less than 800 for each week instead of 35,000 for all weeks combined.

What I want to do, and need help doing is as follows:

The sheet named "Scorecard" is where I enter all of the stats to be collected for each match. On the Scorecard sheet there is a drop down for the week number of the current match being entered, 1 through 48. A range of data, for instance AK112:AU171, is collected from the information entered for each player within the current match on the scorecard. I want to push a button and have the macro look at the week number (Q3), check if there is already a worksheet named as "FullPlayerStatsW1" and if it doesn't exist, create it. "FullPlayerStatsW" would be the name of each sheet followed by the week numbers from 1 through 48. After creating the sheet, the macro should copy the range AK112:AU171 and paste it starting in column A row 1 of the newly created worksheet and paste all other games from other matches for that week at the first blank row found in the new sheet, A:K. All data should be pasted as values only on the FullPlayerStatsW (1-48) sheets.

Let me know if I need to clarify anything. Thanks!!

 

[BeachRock]

I’m not a VBA guru and wouldn’t know where to begin with this. I can record a macro with the best of them but this will take writing code in VBA, which I have no experience doing.

Logically, if this helps at all, the steps would be something like this written in human terms. I'm sorry if I'm being repetitive.

1. A week number is chosen, 1 through 48, from the Scorecard sheet Q3 drop down menu.

2. All data is entered on the Scorecard sheet for each player and each game they played.

3. A button is pressed containing a macro that reads the data in Q3 and combines it with “FullPlayerStatsW” with the week number at the end to result with “FullPlayerStatsW1” for week 1 and “FullPlayerStatsW2” for week 2, etc. The macro then searches the workbook to see if the sheet name “FullPlayerStatsW1” exists yet and if it doesn’t then the sheet is created and named “FullPlayerStatsW1”.

4. The macro continues once it has verified that the sheet exists or after the sheet is created by then copying a range of cells from the Scorecard sheet, AK112:AU171, and then pastes the range into the new sheet in columns A:K on the first row found to have nothing in it.

 

[BeachRock]

Maybe if I break this down into smaller chunks....

I found some code for adding a sheet based on a cell reference.

Sub Add_Worksheet_Name_From_Cell()
Dim NumberSheets As Integer
NumberSheets = ActiveWorkbook.Worksheets.Count
Sheets.Add After:=Sheets(NumberSheets)
ActiveSheet.Name = Sheet1.[Q3].Value
Sheet1.Activate
End Sub

This works well and creates a sheet with whatever data that exists in ScoreCard!Q3 as the new sheet name. But, if the macro runs again after that new sheet is already created I get a runtime error 1004 (cannot rename a sheet to the same name as another sheet) and a new sheet is created as "Sheet4" or the next incremental number.

I found other code for error checking that is supposed to check if the name already exists. I tried to combine the two and came up with the following:

Sub FindCreateSheet()
Dim NumberSheets As Integer
Dim wSheet As Worksheet
On Error Resume Next
Set wSheet = Worksheets("Sheet1")
If wSheet Is Nothing Then
Else
NumberSheets = ActiveWorkbook.Worksheets.Count
Sheets.Add after:=Sheets(NumberSheets)
ActiveSheet.Name = Sheet1.[Q3].Value
Sheet1.Activate
End If
On Error GoTo 0
End Sub

This runs without errors but doesn't create the sheet and essentially does nothing.

Also, I would like the newly created sheets to combine both the cell contents and "FullPlayerStatsW" so the sheet names are that and a number at the end such as "FullPlayerStatsW1", "FullPlayerStatsW2", etc.