Macro to make report more useful

[jtf72]

I'm working on a macro to make an exported report more useful. My intentions are to:

1. Delete unnecessary columns
2. make 30 new worksheets based on the names of "teams" and copy the data from the first sheet to those
3. Now each worksheet has extra rows, delete the extra rows based on the value in the TeamName column
4. Take an average from the "Points Scored" column of each sheet
5. Show the "high number" from the "Points Scored" column of each sheet
6. Take a count for the number of games from each "team" sheet
7. There are different statuses for each team, would like to take a count for each status for each team and add them to the summary worksheet.

Here's what I have so far:

Sub DV_Macro()


'Delete unnecessary columns
    Columns(4).EntireColumn.Delete
    Columns(4).EntireColumn.Delete
    Columns(5).EntireColumn.Delete
    Columns(10).EntireColumn.Delete
    Columns(10).EntireColumn.Delete
    Columns(10).EntireColumn.Delete
    Columns(10).EntireColumn.Delete
    Columns(12).EntireColumn.Delete
    Columns(12).EntireColumn.Delete
    Columns(12).EntireColumn.Delete
    
'Copy worksheet Main to individual team worksheets
Dim i As Integer
    Dim ws As Worksheet
    Dim sh As Worksheet
    Set ws = Sheets("Main")
    Set sh = Sheets("TeamList")
    Application.ScreenUpdating = 0
     
    For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
        Sheets("Main").Copy After:=sh
        ActiveSheet.Name = sh.Range("B" & i).Value
    Next i
    
End Sub

I'm working through this and having a few issues. Hoping to get some input from the experts on this forum.

The columns delete works fine (that was easy).

The worksheet copy portion does work, but it copied all sheets, meaning it duplicated the TeamList and Main sheets which I didn't really want it to do.

I'm researching the rest and I'm sure will post back with more questions.

Thank you!

 

[JLGWhiz]

You can fix the sheets copy portion a couple of ways. One is to use an If...Then statement to eliminate the two sheets you don't want to duplicate.

If sh.Range("B" & i).Value <> "Main" And sh.Range("B" & i).Value <> "Teamlist" Then
	Sheets("Main").Copy After:=sh
        ActiveSheet.Name = sh.Range("B" & i).Value
End If

Or simply delete those two names from the list on the Teamlist sheet.

 

[jtf72]

Thanks for the reply. I haven't had time to revisit this yet. I accomplished most of what I wanted to do with the beginning of the macro and a pivot table, but I want to set up the macro for future weeks.