copy selected cells from one row and paste into last row +1 in another worksheet

[bryanrobson]

Hi. I have the code below. I have selected data in cells A, B, C, F, G and H. UI have copied that row and I want to paste that into another worksheet ws2 / Combined for Lookup into the next row available after the last row.
I dont want to use some form of a loop. Is there a one liner of code I can do that with as I dont want to use a loop to just copy one line and paste straight away into another sheet.

Many thanks


Set WsF = Application.WorksheetFunction
Set ws1 = ThisWorkbook.Sheets("events log")
Set ws2 = ThisWorkbook.Sheets("Combined for Lookup")
For Rw = 2 To 9999
On Error Resume Next
ws1.Cells(Rw, "I") = WsF.VLookup(ws1.Cells(Rw, "B"), ws2.Range("B2:G9999"), 6, False)

If ws1.Cells(Rw, "I") = "n/a" Or _
ws1.Cells(Rw, "I") = "" Then

ws1.Cells(Rw, 1).EntireRow.Font.Bold = True
ws1.Cells(Rw, 1).EntireRow.Font.Color = Black

shtName = "A" & Rw & "," & "B" & Rw & "," & "C" & Rw & "," & "F" & Rw & "," & "G" & Rw & "," & "H" & Rw
Range(shtName).Select
Selection.Copy



End If

Next Rw

 

[Flaiban]

Maybe helps you...

Sub Transfer()
    
    Lrow2 = Sheets("Combined for Lookup").Cells(Rows.Count, 1).End(xlUp).Row + 1
    Lrow1 = Sheets("events log").Cells(Rows.Count, 1).End(xlUp).Row
    Lin = Lrow2
    
    For i = 2 To Lrow1
        If Sheets("events log").Cells(i, 9).Value = "n/a" Or Sheets("events log").Cells(i, 9).Value = "" Then
            Sheets("Combined for Lookup").Cells(Lin, 1) = Sheets("events log").Cells(i, 1)
            Sheets("Combined for Lookup").Cells(Lin, 2) = Sheets("events log").Cells(i, 2)
            Sheets("Combined for Lookup").Cells(Lin, 3) = Sheets("events log").Cells(i, 3)
            Sheets("Combined for Lookup").Cells(Lin, 4) = Sheets("events log").Cells(i, 4)
            Sheets("Combined for Lookup").Cells(Lin, 5) = Sheets("events log").Cells(i, 5)
            Sheets("Combined for Lookup").Cells(Lin, 6) = Sheets("events log").Cells(i, 6)
            Sheets("Combined for Lookup").Cells(Lin, 7) = Sheets("events log").Cells(i, 7)
            Sheets("Combined for Lookup").Cells(Lin, 8) = Sheets("events log").Cells(i, 8)
            Sheets("Combined for Lookup").Cells(Lin, 9) = Sheets("events log").Cells(i, 9)
            
            Lin = Lin + 1
            
        End If
        
    Next
    
    
End Sub

 

[bryanrobson]

Maybe helps you...

Sub Transfer()
   
    Lrow2 = Sheets("Combined for Lookup").Cells(Rows.Count, 1).End(xlUp).Row + 1
    Lrow1 = Sheets("events log").Cells(Rows.Count, 1).End(xlUp).Row
    Lin = Lrow2
   
    For i = 2 To Lrow1
        If Sheets("events log").Cells(i, 9).Value = "n/a" Or Sheets("events log").Cells(i, 9).Value = "" Then
            Sheets("Combined for Lookup").Cells(Lin, 1) = Sheets("events log").Cells(i, 1)
            Sheets("Combined for Lookup").Cells(Lin, 2) = Sheets("events log").Cells(i, 2)
            Sheets("Combined for Lookup").Cells(Lin, 3) = Sheets("events log").Cells(i, 3)
            Sheets("Combined for Lookup").Cells(Lin, 4) = Sheets("events log").Cells(i, 4)
            Sheets("Combined for Lookup").Cells(Lin, 5) = Sheets("events log").Cells(i, 5)
            Sheets("Combined for Lookup").Cells(Lin, 6) = Sheets("events log").Cells(i, 6)
            Sheets("Combined for Lookup").Cells(Lin, 7) = Sheets("events log").Cells(i, 7)
            Sheets("Combined for Lookup").Cells(Lin, 8) = Sheets("events log").Cells(i, 8)
            Sheets("Combined for Lookup").Cells(Lin, 9) = Sheets("events log").Cells(i, 9)
           
            Lin = Lin + 1
           
        End If
       
    Next
   
   
End Sub

thanks a lot. much appreciated

 

[Flaiban]

thanks a lot. much appreciated

You are welcome