VBA Add New Table Row & Keep Formulas

[Tknowles]

Hi Team

I have a 27 column table that I am adding new rows to based on the range C2:AC2. Two issues, 1) some of my input range cells are formulas which are not getting copied to the table when running the VBA (only the values) and 2) it takes awhile for the VBA to be completed - is there a more efficient code?

Sub Addrecord()
Dim ws As Worksheet
Dim newrow As ListRow


Set ws = ActiveSheet
Set newrow = ws.ListObjects("ClientCoverage").ListRows.Add

With newrow

.Range(1) = Range("C2")
.Range(2) = Range("D2")
.Range(3) = Range("E2")
.Range(4) = Range("F2")
.Range(5) = Range("G2")
.Range(6) = Range("H2")
.Range(7) = Range("I2")
.Range(8) = Range("J2")
.Range(9) = Range("K2")
.Range(10) = Range("L2")
.Range(11) = Range("M2")
.Range(12) = Range("N2")
.Range(13) = Range("O2")
.Range(14) = Range("P2")
.Range(15) = Range("Q2")
.Range(16) = Range("R2")
.Range(17) = Range("S2")
.Range(18) = Range("T2")
.Range(19) = Range("U2")
.Range(20) = Range("V2")
.Range(21) = Range("W2")
.Range(22) = Range("X2")
.Range(23) = Range("Y2")
.Range(24) = Range("Z2")
.Range(25) = Range("AA2")
.Range(26) = Range("AB2")
.Range(27) = Range("AC2")
End With


End Sub

 

[Domenic]

First, copy your range . . .

Range("C2:AC2").Copy

Then paste formulas . . .

newrow.Range.PasteSpecial xlPasteFormulas

Hope this helps!