I need help with vba coding to properly create an entry form that inputs data to a master table

Question

In microsoft excel, I have a ws titled "ENTRY". On this ws, I have several inputs one of which is "LINE ITEMS" that asks for a quantity between 1-20. Depending on this input, the amount of rows the entry data will be is assigned into an entry chart. I then would like an "ENTER" or "SUBMIT" button assigned to a macro that will copy this dynamic entry chart of various amounts of rows and add it to the bottom of a master chart on another worksheet entitled "TRACKER". Once this has been input into the Tracker master chart, I would like to delete all inputs on the ENTRY ws to allow for new entries. I have this some of the way there but one issue is I cannot select just the amount of data decided by the number of line items assigned. I also do not know how to paste it with just values (no formatting).

Answer 1

Assuming the quantity is in cell C3 and the data is in cells C4, C5, ..., there are two easy ways to select the data:

1 - Use the End function to select the rows containing data.

Dim Data as Range
Set Data = Range("C4").Offset(Range("C3").value,0)
Set Data = Range(Range("C4"),Data)
Dim Data As Range
Set Data = Range("c4").End(xlDown)
Set Data = Range(Range("C4"), Data)
Data.Select

2 - Use the Offset function to select the specified number of rows.

Dim Data As Range
Set Data = Range("c4").Offset(Range("C3").Value, 0)
Set Data = Range(Range("C4"), Data)
Data.Select

Method 1 stops at the first blank row.

The easiest way to copy and paste the data with the options you want is to record a "temporary" macro while performing the task manually. Then you can copy (and tweak) the code into your "permanent" macro.