Complete noob question: How to copy and paste using VBA?

[s_u_n]

My first post here (I tried several examples, but nothing seems to work, so I registered here).
Here is the story:

I have a list of product names and prices in a sheet called MyList.

Now, I am building a calculator in another sheet (Calc) which needs to use the data from MyList
My Calc sheet has a dropdown list (I have used Data Validation)in cell B2.
I will use INDEX and MATCH or VLOOKUP or something else to get corresponding price displayed in C2.


Now the scenario is:
I expect the user to select any item from the drop down list (B2) and click on a command button "Add" which is located next to cell B2.
On click of the "Add" button, the product name and price should be copied from B2 and C2 to B5 and C5 respectively.
If the user presses the button with another product selected from the dropdown, the copy will still happen from B2 and C2, but paste has to go to the next blank row (which is B6 and C6). This process can continue for upto 250 rows (but in reality about 20 rows).


It is possible that after adding 5-10 items, a user may want to delete one or more items from the previous additions.
So he could simply go to a row, right click and delete any row manually. This should not break the functionality of the "Add" button in any way.

So, the problem is with the code for the "Add" button.



Private Sub CmdBut_Add_Click()

Dim NBR As Long 'Next Blank Row
With Sheets("Calc")

NBR = .Range("B" & .Rows.Count).End(xlUp).Row + 1
Worksheets("Calc").Range("B3").Value = .Range("B" & rw).Value
End With

End Sub

The line with NBR is probably correct if I use the Watch the value, but I have no clue how to copy and paste the values and repeat the process if required.
The line with Worksheets is incorrect in terms of syntax perhaps. Please ignore that one.

Thanks.

 

[patel45]

please upload a sample file (with current data and desired result) on a sharing site, then paste the link here

 

[s_u_n]

please upload a sample file (with current data and desired result) on a sharing site, then paste the link here

Thanks for the interest.
File uploaded here.
Temp Share : MrExcelSample.xlsx | Free temporary file hosting

 

[patel45]

Sub a()
LR = Cells(17, "B").End(xlUp).Row + 1
Range("B" & LR) = Range("B2")
Range("C" & LR) = Range("C2")
End Sub

 

[s_u_n]

Sub a()
LR = Cells(17, "B").End(xlUp).Row + 1
Range("B" & LR) = Range("B2")
Range("C" & LR) = Range("C2")
End Sub

Excellent.
However after deleting one record from the quotation section, when I click the Add button, it inserts the data after the Total. Now the function just does not work anymore.

Something is still wrong.

New file uploaded.
Temp Share : MrExcelSample.xlsm | Free temporary file hosting

 

[patel45]

Private Sub BtAdd_Click()
LR = Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range("B4:B" & LR).Find(What:="total", LookIn:=xlValues)
Rtotal = Rng.Row - 1
LR = Cells(Rtotal, "B").End(xlUp).Row + 1
Range("B" & LR) = Range("B2")
Range("C" & LR) = Range("C2")
End Sub

 

[s_u_n]

Thank you patel45.
This code is more robust and does the job well.