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.