VBA Macro

[djreyes]

Hi, 

Please let me know if I am missing something. 

'Find the next available row in the destination sheet
nextRow = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp).Row + 1

Instead of going to the first row, its going to the second row. 

Please see attached screencapture. 

Thanks for the help!

[#2]

@djreyes 

If the entire column is still empty, destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp).Row will be 1, so if you add 1 you end up with 2.

You could use

    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

[#3]

I tried and it didnt work, should I add it like this?

' Find the next available row in the destination sheet
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

[#4]

@djreyes 

The code that I posted should replace the code that you included in the first post. How you use nextRow is up to you.

The code calculates nextRow correctly - I tested it. What is the problem you're having with it?

[#5]

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

[#6]

@djreyes 

Could you attach a sample workbook that demonstrates the problem (without sensitive data), or if that is not possible, make it available through Google Drive, OneDrive, Dropbox or similar?