Excel: Finding the next empty cell


I am using the code below for a copy and paste operation. It is assigned to a button. My problem is it sends the first data no problem but when I repeat the operation it pastes over the last one. I need it to continually find the next empty cell. Any Advice is appreciated. Truth be told I would like to do this without having to select the sheets when pasting.

Dim r As Long
        For r = 1 To 150
        If Cells(r, 1).Value > "" And Cells(r, 10).Value = "" Then Exit Sub
        Next r
        Sheets("Purchasing").Select
        Sheets("Purchasing").Unprotect
        Sheets("Shopping Cart").Select
        Range("A3").Select
        ActiveSheet.Range(Cells(3, 1), Cells(100, 11)).Select
        Selection.Copy
        Sheets("Purchasing").Select
        Range("A2").Select
        Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("Purch").Select
        Sheets("Purchasing").Protect AllowFiltering:=True
        Sheets("Shopping Cart").Select
        Application.CutCopyMode = False
        Range("H3:A3", "I3:J3").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
        Range("A3").Select
MsgBox "Thank You For Your Order"


This question generated 17 answers. To proceed to the answers, click here.

This thread is current as of November 30, 2015.


For more resources for Microsoft Excel