vba - Excel Data Replication + Automated Saving PT 2

I am trying to replicate data entered from one worksheet(sheet1) into another(sheet2) and then have it save hourly on a separate line each time on sheet2. I am pulling unique cells from each row rather than the entire row from sheet1 to be saved to sheet 2 with all data being in a specific order and outputting to a single row with one value per cell and creating a new line each time it is saved. For my usage, Sheet 1 will always stay open as the active sheet where changes will be made and the data will periodically save to Sheet2 while sheet1 remains selected. I am saving every 5 seconds at this stage for troubleshooting purposes.

I need assistance on pulling values from unique cells on approximately 30 rows from sheet1 and saving it to specific cells on sheet2 while sheet1 remains open and active.

I am having the following issues so far:
1. the data will replicate on sheet1 instead of sheet2 when i have sheet1 selected and open instead of writing to sheet2 as i need it to when sheet1 is being viewed/modified actively.

here is my code so far:

Option Explicit
Public dTime As Date

Sub ValueStore()
Dim dTime As Date
    Range("A" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("A2").Value
    Range("B" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("B2").Value
    Range("C" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("C2").Value
    Range("D" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("D2").Value
    Range("E" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("E2").Value

    Range("F" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("F2").Value
    Range("G" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("G2").Value
    Range("H" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("H2").Value
    Range("I" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("I2").Value
    Range("J" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("J2").Value
    Range("K" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("K2").Value
    Range("L" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("L2").Value
    Range("M" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("M2").Value
    Range("N" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("N2").Value
    Range("O" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("O2").Value

    Range("P" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("P2").Value
    Range("Q" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("Q2").Value
    Range("R" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("R2").Value
    Range("S" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("S2").Value
    Range("T" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("T2").Value
    Range("U" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("U2").Value
    Range("V" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("V2").Value
    Range("W" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("W2").Value
    Range("X" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("X2").Value
    Range("Y" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("Y2").Value

    Range("Z" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("Z2").Value
Range("AA" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("AA2").Value
Range("AB" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("AB2").Value
Range("AC" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("AC2").Value
Range("AD" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("AD2").Value
Range("AE" & Cells(Rows.Count).Row).End(xlUp).Offset(1, 0).Value = Range("AE2").Value


  Call StartTimer1
End Sub



Sub StartTimer1()
    dTime = Now + TimeValue("00:00:05")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub

Sub StopTimer1()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False

End Sub

Answer

Here is a sample of your code with the additions and changes.

1-Create worksheet variables
2-Make the last row a variable
3-Since your are writing to sheet2, put your code inside a With - End With statement
4-Ensue you put the ws1 variable in front of the range you are copying from

Dim dTime As Date

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = ActiveWorkbook.Worksheets("Sheet1")
Set ws2 = ActiveWorkbook.Worksheets("Sheet2")

Dim lRow As Long
    lRow = ws2.Range("A" & Rows.Count).End(xlUp).Row


With ws2
    Range("A1:A" & lRow).Offset(1).Value = ws1.Range("A2").Value
    Range("B1:B" & lRow).Offset(1).Value = ws1.Range("B2").Value
    Range("C1:C" & lRow).Offset(1).Value = ws1.Range("C2").Value
End With