VBA for adding data to new row in table whilst retaining formula?

[bert1984]

Hi!

I'm new to the forum and was wondering if someone out there could give me a little guidance.

I recently watched the linked Youtube video which detailed how to add a new line containing data into a table using a macro button. Whilst the video was great and was incredibly helpful I am still struggling with one thing related to this, how to add data to a table with its corresponding formula.

For example, in the video when you use the formula "myRow.Range(1)=Range("B2")" this takes the information as it appears in the cell and enters it exactly, even if there was formula in the cell. Essentially it pastes values and not formulae.

Is there a way to do this using the same code format as in the video or do I need to start from scratch?

Any help would be greatly appreciated.

 

[RoryA]

Welcome to the forum.

Formulas in a table should automatically fill down to new rows without you having to code them, or did I misunderstand your question?

 

[bert1984]

Hi Rory,

Thanks for the quick reply.

I understand that tables can automatically copy formula down however this isn't the case for what I am doing.

In the below image if I were to change the discount in the table on the left (i.e. the Quotation) then nothing would happen as only values have been entered from the table on the right.

What I want to achieve is if I click the "Add To Quote" button, all the cells from the table on the right are entered into the table on the left including their formulae. This means that if I should want to change a customers discount on a variety of items, I can do it on the table on the left easily.

The current VBA code i am using is: -

Private Sub cmdAdd_Click()
Dim myRow As ListRow
Dim introws As Integer

introws = ActiveWorkbook.Worksheets("Quotation Sheet").ListObjects("DATA").ListRows.Count
Set myRow = ActiveWorkbook.Worksheets("Quotation Sheet").ListObjects("DATA").ListRows.Add(introws)

myRow.Range(1) = Range("I2")
myRow.Range(2) = Range("J2")
myRow.Range(3) = Range("K2")
myRow.Range(4) = Range("L2")
myRow.Range(5) = Range("M2")
myRow.Range(6) = Range("N2")
myRow.Range(7) = Range("O2")

End Sub


Thanks again for any help :D