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.
Truth be told I would like to do this without having to select the sheets when pasting.
Code:
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"
Last edited by a moderator: