My VBA code overwrites my last row. How can I write the code to put new information on the next empty row?

I followed a YouTube tutorial to copy data from one sheet into another sheet in the same workbook. Mind you, I have no experience in VBA.

I noticed that when I add a new entry it overwrites the previous row. I want the code to find the last empty row and add the newest entry there. So, if rows 1 and 2 have information, then the newest information gets transferred to row 3 and so forth.

Here is the code:

Private Sub CommandButton1_Click()
'Create and set variables for the Time Tracker & Master Data worksheets
Dim TTrk As Worksheet, MDat As Worksheet

Set TTrk = Sheet1
Set MDat = Sheet2

'Create and set variables for each cell in the Time Tracker sheet
Dim Name As Range, AddDate As Range, TotalHours As Range, Activity As Range, Client As Range, Category     As Range, AddHours As Range, TimeSpent As Range, Additional As Range

Set Name = TTrk.Range("C2")
Set AddDate = TTrk.Range("C3")
Set TotalHours = TTrk.Range("C5")
Set Activity = TTrk.Range("B8:B15")
Set Client = TTrk.Range("C8:C15")
Set Category = TTrk.Range("D8:D15")
Set AddHours = TTrk.Range("E8:E15")
Set TimeSpent = TTrk.Range("F8:F15")
Set Additional = TTrk.Range("G8:G15")

'Create a variable for the paste cell in the Master Data worksheet
Dim DestCell As Range

If MDat.Range("A2") = "" Then 'If A2 is empty
Set DestCell = MDat.Range("A2") '...then destination cell is A2
Else
Set DestCell = MDat.Range("A1").End(xlDown).Offset(1, 0) '...otherwise the next empty row

If MDat.Range("A2") = "" Then 'If A2 is empty
Set DestCell = MDat.Range("A2") '...then destination cell is A2
Else
Set DestCell = MDat.Range("A1").End(xlDown).Offset(2, 0) '...otherwise the next empty row

End If

'Copy and paste data from the Time Tracker worksheet to the Master Data worksheet
Name.Copy DestCell
AddDate.Copy DestCell.Offset(0, 1)
TotalHours.Copy DestCell.Offset(0, 2)
Activity.Copy DestCell.Offset(0, 3)
Client.Copy DestCell.Offset(0, 4)
Category.Copy DestCell.Offset(0, 5)
AddHours.Copy DestCell.Offset(0, 6)
TimeSpent.Copy DestCell.Offset(0, 7)
Additional.Copy DestCell.Offset(0, 8)

'Clear the contents in the Time Tracker worksheet
AddDate.ClearContents
TotalHours.ClearContents
Activity.ClearContents
Client.ClearContents
Category.ClearContents
AddHours.ClearContents
TimeSpent.ClearContents
Additional.ClearContents

End Sub

I tried taking away certain coding and trying to find coding to add into my current code to see if it would resolve the issue, but I have no experience in VBA.

Copy Ranges Side-By-Side

A Quick Fix

• Reference the destination cell in the following way:

<!-- language: lang-vb -->

Dim DestCell As Range: Set DestCell = MDat.Range("A2")
With MDat.UsedRange
Dim drOffset As Long:
drOffset = .Rows(.Rows.Count).Row - DestCell.Row + 1
If drOffset > 0 Then
Set DestCell = DestCell.Offset(drOffset)
End If
End With

Before

After

A Simplification

<!-- language: lang-vb -->

Private Sub CommandButton1_Click()
Const SRC_RANGES As String = "C2,C3,C5,B8:G15"
Dim sClearFlags(): sClearFlags = VBA.Array(0, 1, 1, 1)
Const DST_FIRST_CELL As String = "A2"
Dim sws As Worksheet: Set sws = Sheet1
Dim dws As Worksheet: Set dws = Sheet2
Dim dfCell As Range: Set dfCell = dws.Range(DST_FIRST_CELL)
With dws.UsedRange
Dim drOffset As Long: drOffset = .Rows(.Rows.Count).Row - dfCell.Row + 1
If drOffset > 0 Then
Set dfCell = dfCell.Offset(drOffset)
End If
End With
With sws.Range(SRC_RANGES)
Dim arg As Range, n As Long
For Each arg In .Areas
' Copy.
arg.Copy dfCell
Set dfCell = dfCell.Offset(, arg.Columns.Count)
' Clear.
If sClearFlags(n) = 1 Then
arg.ClearContents
End If
n = n + 1
Next arg
End With
End Sub