excel VBA code for copy/paste on next row

Hello kcanales and welcome to the forum,

Posting your code as you have is "OK" but it is better to use code tags. To do so, click "CODE" at the top and then delete Code_Goes_Here and paste in your code. Better still is to use the "Add Files" button and attach a sample file to clearly show what you are trying to achieve.

You mention that "the macros runs fine" which I find puzzling give the errors in your code. Copying data from one sheet to another is something that is done regularly. The first thing needed in your code is to determine the next available row.

Dim LR As Long  ' last used row on worksheet 
' find the last used row; the next blank row will be LR + 1 
LR = Worksheets("Hold Log").Cells(Rows.Count, 1).End(xlUp).Row

For each cell on the "Ontario" worksheet that you want to copy to the "Hold Log" worksheet can be accomplished with one simple line of code.

Sheets("Ontario").Range("B3").Copy Sheets("Hold Log").Range("A" & LR + 1)

Repeat this line for each piece of data - update the cell references as needed.

This can also be accomplished using code which is a bit longer but easier to understand for newbies.

Worksheets("Ontario").Range("B3").Copy _
    Destination:=Worksheets("Hold Log").Range("A" & LR + 1)

In the attached file I made I have put both macros in it with buttons to click to run the macros. As you will see, it doesn't matter which sheet is active or which button you click, the result will be the same - the data will be copied from "Ontario" to "Hold Log".

If this fixes things for you please mark my answer as Selected.

Cheers   :-)