VBA - copy new rows from different sheet, if condition is met

[lotrev]

Hi, I am VBA Excel coder since yesterday and I am stuck. Hopefully you can help me.
I am managing sports team and want to follow players training progress.
I have made google forms page and linked it's workbook to my computers excel file to update it automatically via Data - New Query - From Web.
The Data comes in like this
[TABLE="width: 607"]
<colgroup><col span="2"><col><col span="3"><col></colgroup><tbody>[TR]
[TD]Column1[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[TD]Column4[/TD]
[TD]Column5[/TD]
[TD]Column6[/TD]
[TD]Column7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]Timestamp[/TD]
[TD]Name[/TD]
[TD]XXXX[/TD]
[TD]YYYYY[/TD]
[TD]ZZZZZ[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]15/11/2019 14:12:02[/TD]
[TD]Player1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

The data will come in as above written. I want to write a code that looks at column 4 to fine needed condition. I have different sheets for each players, so Player1 data would copy to sheet Player1 and so on.
Best would be to copy only column 4-7 data, because I don't need first threes, but I can hide them as well, so entire row could work too.

Problems that I have discovered with different codes
Rows copy again and again - I triggered copying with active button, but it copied old data as well. I need new imported rows only.
Data copied as text - I would prefer numbers, so I can make graphics immediately. Now I need to format cells always.

 

[mumps]

When you say "to fine needed condition", what condition are you looking for? Are you looking for a particular player name and then copy columns 4 to 7 to that players sheet? If so, how do you determine which player? Do you want to be prompted to enter the player's name? Please clarify in detail.

 

[lotrev]

When you say "to fine needed condition", what condition are you looking for? Are you looking for a particular player name and then copy columns 4 to 7 to that players sheet? If so, how do you determine which player? Do you want to be prompted to enter the player's name? Please clarify in detail.

Simplified version wouldbe If value = "player1" then copy to worksheets(''player1'')
I would search on column 4, where all player names would be(player1, player2, ...), so I would copy row with needed player to it's specific sheet.
I would have 1 main sheet with all data and ~14 smaller ones with only specified player data.

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long, player As Range, key As Variant, RngList As Object, fnd As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Set RngList = CreateObject("Scripting.Dictionary")
    For Each Rng In Range("D2:D" & LastRow)
        If Not RngList.Exists(Rng.Value) Then
            RngList.Add Rng.Value, Nothing
        End If
    Next
    For Each key In RngList
        Set fnd = Sheets(key).Range("A:A").Find(key, LookIn:=xlValues, lookat:=xlWhole)
        If fnd Is Nothing Then
            Range("B1:G" & LastRow).AutoFilter Field:=3, Criteria1:=key
            Range("D2:G" & LastRow).SpecialCells(xlCellTypeVisible).Copy Sheets(key).Cells(Sheets(key).Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
        Range("B1").AutoFilter
    Next key
    Application.ScreenUpdating = True
End Sub

 

[lotrev]

This is my main sheet, all data comes in here. The button is ActiveX button(used it with different code, not sure it is correct here also).
I want all data in name column with name ''Anna'' copy to sheet Anna. Similar with ''Zane''. And so on.
I just copied this code like this

Nothing happens. Maybe somewhere is tutorial for this. I am noob in this VBA stuff.