Pull Data from One Sheet to Another

Hi Folks,
I am trying to create a macro that will pull some data from different cells in one worksheet and posting it to another worksheet within the same workbook. I am trying to get my head around coding this (total Excel noob here) but having a heck of a time.
If someone can take a look at what I have and let me know what I am doing wrong, I would greatly apreciate it.
Thanks.

Sub DatabaseTest
'
' DatabaseTest Macro
'

'
Dim wsSource As Worksheet, wsDest As Worksheet
    Dim Nextrow As Long
    
    Set wsSource = Sheets("Purchase")
    Set wsDest = Sheets("Sheet1")
    
    ' Next empty row on the destination worksheet
    Nextrow = wsDest.Cells.Find("*", , , , xlByRows, xlPrevious).Row + 1

    ' Date
    wsDest.Range("A2" & Nextrow).Value = wsSource.Range("L1").Value
    ' Ticket
    wsDest.Range("B2" & Nextrow).Value = wsSource.Range("L3").Value
    ' Client
    wsDest.Range("C2" & Nextrow).Value = wsSource.Range("D2").Value
    ' Steel Spot
    wsDest.Range("D2" & Nextrow).Value = wsSource.Range("F8").Value
    ' Steel Weight
    wsDest.Range("E2" & Nextrow).Value = wsSource.Range("E20").Value
    ' Steel Total
    wsDest.Range("F2" & Nextrow).Value = wsSource.Range("G20").Value
    ' Copper Spot
    wsDest.Range("G2" & Nextrow).Value = wsSource.Range("F22").Value
    ' Copper Weight
    wsDest.Range("H2" & Nextrow).Value = wsSource.Range("E28").Value
    ' Copper Total
    wsDest.Range("I2" & Nextrow).Value = wsSource.Range("G28").Value
    ' Aluminum Spot
    wsDest.Range("J2" & Nextrow).Value = wsSource.Range("F30").Value
    ' Aluminum Weight
    wsDest.Range("K2" & Nextrow).Value = wsSource.Range("E40").Value
    ' Aluminum Total
    wsDest.Range("L2" & Nextrow).Value = wsSource.Range("G40").Value
    ' Total Purchase
    wsDest.Range("M2" & Nextrow).Value = wsSource.Range("G41").Value
    
    

   
End Sub

Hi,

You may not even need a macro since probably using the COUNTA() function would be enough to return a value for an empty cell. Difficult to comment with certainty without actually seeing your workbook.

However the syntax of your code is wrong.

Instead of

wsDest.Range("A2" & Nextrow).Value = wsSource.Range("L1").Value

try instead:

wsDest.Range("A2").Cells(Nextrow,1).Value = wsSource.Range("L1").Value

Regards

Hi Joe,

See the attached. This is the technique I generally use for stuff like this. Note the additional two rows on the Purchases Sheet with A2:M2 named 'New_Record"
Now the two line macro simply copies this row and pastes it as values to the data sheet.