Search code examples
excelvbarowtranspose

next available row & transposing

I have data on one worksheet in C7:C18. This data needs to copied to the next available row on a different worksheet. I have been on this for hours and I just cant get it to work.

I have managed to get the transpose part to work but in constantly overwrites the same row.

    Private Sub SaveCust()
    Dim NRow As Long
    Worksheets("CustDB").Select
    NRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    Worksheets("CustDB").Range("a" & NRow & ":L"& NRow).Value = 
         WorksheetFunction.Transpose(Worksheets("Customer").Range("c7:c18"))

    End Sub
Solution 
  • Private Sub SaveCust()
Dim cus, cdb As Worksheet
Dim NRow As Long

Set cus = Worksheets("Customer")
Set cdb = Worksheets("CustDB")

NRow = cdb.Cells(Rows.Count, "A").End(xlUp).Row + 1


cdb.Range("a" & NRow & ":L" & NRow).Value = WorksheetFunction.Transpose(cus.Range("c7:C18").Value)

End Sub