Copy data and paste to another column incrementing that column to next empty cell

[edens2021]

Good morning,

I have real time data that is being imported to an excel document. When that data is recorded in cell A2 it then is copied to D2. A new data point will be recorded everyday and I want to have Column D increment by 1 every time a new data point is collected. Along with having it do that. I will need it not rewrite the previous data. So I will need it to locate the next empty cell in the column and record the data in that cell.

Example:

Day 1: A2=395; D2=395
Day 2: A2=386; D3=386
Day 3: A2=109; D4=109
And so on.

Any help would be greatly appreciated. Below is the code I have up to now.

Thanks,

James

Private Sub stamp_DataReady()

On Error GoTo Data_Error
Dim DataVal() As String
Dim data As String

While Stamp.gotData = True
  data = Stamp.GetData
  
  If data <> "" Then
    DataVal = Split(data, ",")

    Select Case DataVal(0)
    Case "CMD?"
       
       Row = 1
        
    Case "DATA"
        cc = countChar(data, ",")
        Row = 2
        txtStatus2 = "Data Excepted" & (Row - 1)
        If Row < 65000 Then
            For x = 1 To cc Mod 27
            Worksheets(1).Range(Chr(64 + x) & CStr(Row)).Value = ReplaceData(DataVal(x))
            Next
            Range("B2").Copy
            Range("D1").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
            D = Columns(4).SpecialCells(11).D + 1
                                 
        End If

[JOHN H. DAVIS]

Maybe:

Sub edens2021()
On Error GoTo Data_Error
Dim DataVal() As String
Dim data As String

While Stamp.gotData = True
  data = Stamp.GetData
  
  If data <> "" Then
    DataVal = Split(data, ",")

    Select Case DataVal(0)
    Case "CMD?"
       
       row = 1
        
    Case "DATA"
        cc = countChar(data, ",")
        row = 2
        txtStatus2 = "Data Excepted" & (row - 1)
        If row < 65000 Then
            For x = 1 To cc Mod 27
            Worksheets(1).Range(Chr(64 + x) & CStr(row)).Value = ReplaceData(DataVal(x))
            Next
            Range("B2").Copy
            Range("D" & Rows.Count).End(3)(2).PasteSpecial (xlPasteAll)
            'D = Columns(4).SpecialCells(11).D + 1
                                 
        End If
End Sub

[edens2021]

Thank you for the help. It works great but I decided to implement a table to go along with my data points. That way it will be easier to handle the data and increase with the data points. However, using the code above. It places the data at the end of the table. What changes would I need to make in order to have it place the data within the table?