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

lotrev

New Member
Joined
Nov 15, 2019
Messages
3
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
Column1Column2Column3Column4Column5Column6Column7
1TimestampNameXXXXYYYYYZZZZZ
215/11/2019 14:12:02Player1143

<colgroup><col span="2"><col><col span="3"><col></colgroup><tbody>
</tbody>

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

Well-known Member
Joined
Apr 11, 2012
Messages
8,446
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.
 
Last edited:

lotrev

New Member
Joined
Nov 15, 2019
Messages
3
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

Well-known Member
Joined
Apr 11, 2012
Messages
8,446
Try:
Code:
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

New Member
Joined
Nov 15, 2019
Messages
3
2019-11-18.png

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
2019-11-18 (1).png

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

Forum statistics

Threads
1,078,522
Messages
5,340,958
Members
399,399
Latest member
SravanaSandhya

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top