VBA Macro

Its still not going to the first row.

Please see below what I have added,

' Dim rngLastCell As Range
Dim rngLastCell As Range
Set rngLastCell = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp)
If rngLastCell.Value = "" Then
nextRow = 1
Else
nextRow = rngLastCell.Row + 1
End If

' Set the destination range for column A (first column)
Set destinationRange = destinationSheet.Cells(nextRow, 1)
' Add the value from cell D10 to the destination range, formatted as DD-MMM-YYYY
destinationRange.Value = Format(sourceSheet.Range("D10").Value, "dd-mmm-yyyy")

' Set the destination range for column B (second column)
Set destinationRange = destinationSheet.Cells(nextRow, 2)
' Add the value from cell D9 to the destination range
destinationRange.Value = sourceSheet.Range("D9").Value

' Set the destination range for the transposed values (starting from column C)
Set destinationRange = destinationSheet.Cells(nextRow, 3)

' Transpose the values from the source range to the destination range
numValues = sourceRange.Rows.Count
For i = 1 To numValues
destinationRange.Offset(0, i - 1).Value = sourceRange.Cells(i).Value
Next i

' Clear the contents of input cells
sourceSheet.Range("D9:D10").ClearContents
sourceRange.ClearContents