Excel VBA copy cells on key press

Excel VBA copy cells on key press

I'm using VBA to dynamically change the worksheet while typing into
another cell. To do so, I've been using the API code that is found here:
Excel track keypresses
so the Sub Sheet_Keypress describes the desired action upon pressing a
key. However, I've been running into problems with the following:
Private Sub Sheet_KeyPress(ByVal KeyAscii As Integer, _
ByVal KeyCode As Integer, _
ByVal Target As Range, _
Cancel As Boolean)
Dim Col As String
Col = Chr(KeyAscii)
Worksheets(1).Range("G" & 4 & ":G" & 6).Value = _
Worksheets(1).Range(Col & 1 & ":" & Col & 3).Value
End Sub
When I go back to the sheet and type somewhere not in rows 1-3, the first
keypress does fine. However, the second keypress is not recorded, and a
further key gives Error 1004. What exactly is causing this error and is it
possible to avoid it?