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

lotrev

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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,788
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top