Paste to row based on row number value in cell

Question

I am trying to write a macro that copies data from a form (Contractor Entry Form, range "U5:AT5") and pastes it to a database (CONTRACTOR DATABASE).

When a record is edited, it requests the Employee ID# and finds that row on the database, and pastes that row number reference temporarily into cell Contractor Entry Form "L1".

I need to then paste the copied data to the database on that row number (-1) that is referenced in cell "L1". If there is no value in "L1" that means it is a new entry and should then just paste to the last row -- as opposed to pasting over a previous record row.

Help, please. My code is here--

Sub ContractorEntry
    
    Range("U5:AT5").Copy
    Sheets("CONTRACTOR_DATABASE").Select
    
    Dim R As Integer
    R = Worksheets("CONTRACTOR ENTRY").Range("L1").value

    'note-- if there is a value in CONTRACTOR ENTRY L1>0 then 
    '  (it represents a row number --- paste value to that row -1 onto 
    '  Contractor Database sheet.

    If Worksheets("CONTRACTOR ENTRY").Range("L1") > 0 Then
        Sheets("CONTRACTOR_DATABASE").Cells (R -1, 1)
        Selection.PasteSpecial
    End If
    
    Else
        'if there is no value in cell L1 then the following to just paste to the next blank row
        lMaxRows = Cells(Rows.Count, "A").End(xlUpSelection.PasteSpecial.Row
    
        Range("A" & lMaxRows + 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks :=False, Transpose:=False
    
       'This returns to the contractor entry form and clears contents
        Sheets("CONTRACTOR ENTRY").Select
        Range("D3:M1").Select
        Selection.ClearContents
      
       'Should go back to Contractor Entry Form for Name and a new entry in cell D3
       Range("D3").Select
    
End Sub

score 1 · Answer 1 · answered Jul 27 '22 at 06:06

There's almost never any need to use Select/Activate see here for guidelines on how to improve your code: How to avoid using Select in Excel VBA

Something like this should work:

Sub ContractorEntry()
    Dim rw, wsInput As Worksheet, wsDB As Worksheet
    
    'use worksheet varaibles for easier maintenance
    Set wsInput = ThisWorkbook.Worksheets("CONTRACTOR ENTRY")
    Set wsDB = ThisWorkbook.Worksheets("CONTRACTOR_DATABASE")
    
    rw = wsInput.Range("L1").Value - 1
    
    'if row not present then get next empty row
    If rw < 1 Then rw = wsDB.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    'copy over values directly (no copy/paste)
    With wsInput.Range("U5:AT5")
        wsDB.Cells(rw, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    
    With wsInput
        .Activate
        .Range("D3:M1").ClearContents
        .Range("D3").Select
    End With
End Sub

Paste to row based on row number value in cell

1 Answers1