Button to transfer to specific cells based on specific reference

[berry9]

Hi guys,
Thoughts on this, I'm working on a poker game tracker & I'm stuck on the coding for a data transfer button.
I have two sheets, first is 'Current game', second is 'Past games'
The idea is to hit the transfer button and transfer the current game data into the corresponding game cells into 'Past games'. For example, set game number to 6 on 'Current game' sheet, input game data results for each player and hit button to transfer player data to corresponding game number row in 'Past games'.
If Player 1, 3 & 6 played, then data from Player 1,3 & 6 will transfer to corresponding row under Player 1, 3 & 6 column.
The data transferred is amount in & amount out from "Current game":
.
Input:
Game number reference: Current game'!C6 [with drop down list with game numbers (1-100)]
"Player" column: 'Current game'!C7:C27 (Drop down list each cell Player 1, Player 2.etc)
"Amount in" column: 'Current game'!U7:U26
"Amount out" column: 'Current game'!V7:V26
Output:
Game number column: 'Past games'!A3:A102 (Game 1-100)
Player row: 'Past games'!C1:CX1 (4 merged cells for each player & "Player 1" cell=Player1 .etc
Player 1 Amount In: C3
Player 1 Amount Out: D3 (Top player reference uses 4merged cells, so Player 2 In would be G1, out H1.etc
.
Example for game 1 if players 1,3 & 6 played would be: (if "Current game!C6 set to "1") and data transferred:
Player 1 amount in: Past games'!C3 - transferred from 'Current game'!(lookup player cell id, transfer from corresponding indexed cell(amount in) )
Player 1 amount out:D3 " "
Player 3 amount in: K3 " "
Player 3 amount out: L3 " "
Player 6 amount in: W3 " "
Player 6 amount out: X4 " " .etc
If no player reference, leave blank
This would all be in game 1 row (from A3). The player IDs can be in different cells in 'Current game' depending on who's playing and in what order they join,
I thought about switching players to columns & games to rows on 'past games' to match 'current game' to make it easier but want to see thoughts on this first, I've attached an example screenshot.
Another thought is data protection, if wrong game number is chosen and values already exist in 'Past games' row then do not transfer or transfer to next empty cell/dummy sheet, not sure on this.
I'm new to excel so still learning, let me know your thoughts or if there's a simpler way to do it, cheers

 

[Legacy 143009]

How about this:

Sub test()
  Dim playerColumn As Long, gameRow As Long, gameNumber As Long, lastPlayer As Long
  With Worksheets("Current Game")
  gameNumber = .Range("C6").Value
  lastPlayer = .Cells(Rows.Count, 3).End(xlUp).Row
  Application.ScreenUpdating = False
  For r = 7 To lastPlayer
    playerColumn = Application.Match(.Cells(r, 3).Value, Worksheets("Past Games").Range("1:1"), 0)
    gameRow = Application.Match(gameNumber, Worksheets("Past Games").Range("A:A"), 0)
    For c = 0 To 1
      If Worksheets("Past Games").Cells(gameRow, playerColumn + c).Value = "" Then
        Worksheets("Past Games").Cells(gameRow, playerColumn + c).Value = .Cells(r, c + 21).Value
      Else
        MsgBox "This record already exist!"
      End If
    Next
  Next
  Application.ScreenUpdating = True
  End With
End Sub

 

[berry9]

How about this:

Sub test()
  Dim playerColumn As Long, gameRow As Long, gameNumber As Long, lastPlayer As Long
  With Worksheets("Current Game")
  gameNumber = .Range("C6").Value
  lastPlayer = .Cells(Rows.Count, 3).End(xlUp).Row
  Application.ScreenUpdating = False
  For r = 7 To lastPlayer
    playerColumn = Application.Match(.Cells(r, 3).Value, Worksheets("Past Games").Range("1:1"), 0)
    gameRow = Application.Match(gameNumber, Worksheets("Past Games").Range("A:A"), 0)
    For c = 0 To 1
      If Worksheets("Past Games").Cells(gameRow, playerColumn + c).Value = "" Then
        Worksheets("Past Games").Cells(gameRow, playerColumn + c).Value = .Cells(r, c + 21).Value
      Else
        MsgBox "This record already exist!"
      End If
    Next
  Next
  Application.ScreenUpdating = True
  End With
End Sub

This works great, thank you for the help!

 

[berry9]

How about this:

Sub test()
  Dim playerColumn As Long, gameRow As Long, gameNumber As Long, lastPlayer As Long
  With Worksheets("Current Game")
  gameNumber = .Range("C6").Value
  lastPlayer = .Cells(Rows.Count, 3).End(xlUp).Row
  Application.ScreenUpdating = False
  For r = 7 To lastPlayer
    playerColumn = Application.Match(.Cells(r, 3).Value, Worksheets("Past Games").Range("1:1"), 0)
    gameRow = Application.Match(gameNumber, Worksheets("Past Games").Range("A:A"), 0)
    For c = 0 To 1
      If Worksheets("Past Games").Cells(gameRow, playerColumn + c).Value = "" Then
        Worksheets("Past Games").Cells(gameRow, playerColumn + c).Value = .Cells(r, c + 21).Value
      Else
        MsgBox "This record already exist!"
      End If
    Next
  Next
  Application.ScreenUpdating = True
  End With
End Sub

Will this code still be effective if I assign unique names to player ids on both sheets?
Example: Instead of "Player 1", "Player 2", would be "Fred", "Steve" .etc
Thanks again !

 

[Legacy 143009]

Will this code still be effective if I assign unique names to player ids on both sheets?
Example: Instead of "Player 1", "Player 2", would be "Fred", "Steve" .etc
Thanks again !

Yeap