Auto Populate a range of data

Rick

Thanks for the expanded question and comment that you prefer not to use a drop down list.

In my revised answer/ attached file, I've added a VBA macro (see code below).

It uses the worksheet event SelectionChange to detect when another cell is picked from whatever data is in column A of sheet1. Pick a name from column A and the stats in K2:P7 will change (but will be blank for my addition Phil since there's no player data on Sheet2).

I've added comments to explain what's going on:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim LastRowA As Integer

' Check that selected cell is in the player column
LastRowA = Range("A" & Rows.Count).End(xlUp).Row 'find last row of column A containing data
If Intersect(Target, Range("A2:A" & LastRowA)) Is Nothing Then Exit Sub

' Copy player name to results section and clear stats
Range("K2").Value = Target.Value
Range("K3:P7").Value = ""

'Find target name in stats page
Dim Names As Range
With Sheet2
    Set Names = .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
    'Cycle through used cells to find player name
    For Each Cll In Names
        If Cll.Value = Target.Value Then
        'If found, copy stats to Sheet1
            Sheet1.Range("L3:P7").Value = Cll.Offset(0, 1).Resize(5, 5).Value
        End If
    Next Cll
End With

End Sub

Note that it relies on each player's data in sheet 2 being in the same format- see Joe's stats (shaded pale yellow, noting that I moved his name into A2 to match how Frank's stats were presented).

You can add your other players to Sheet2 in the same format and the macro will continue to work.

The stats just change on Sheet 1 as you pick players but, if necessary, the colour of the K2:P7 could be toggled to show a change has been made. I haven't done that yet.

I don't know anything about baseball but hope this makes sense and helps.

Hello Avsrick,

I was looking at posts which didn't indicate "Answer Selected" and came upon yours. With baseball season just around the corner I decided to find a possible answer to your question. It intrigued me as something I could solve and possibly learn from (which I did). In my attached file I have added sheets to your original file – "Player Stats" (sheet3 - modification of your sheet1) and "Team Stats" (sheet4 - modification of your sheet2). Both sheets have added notes to help explain how everything works.

On the "Team Stats" sheet select a player from the drop down list ("B2") and then click on the "Get Player's Stats" button to display that player's stats in the table to the right. Click on the "Update Players List" button to ensure the drop down list is current.

On the "Team Stats" sheet, if you need to update a player's stats, select the player from the drop own list ("J1") and then click on the "Update Player's Stats" button. If you want to add a new player click on the "Add New Player" button.

Feel free to edit/adapt macros as needed to suit your specific needs.

For the 'buttons' I used shapes – just like Don prefers – as they can be easily modified for more visual appeal.

If you like my suggestion please mark it as "Selected Answer".