Write Range to Table-CodePudding

I am trying to write a range to table. Most of the time I'd just write an array to the table but this time I need to preserve formulas across my macro. I copy pasted my code from writing an array and tweaked it but across all my variations, I can't make it work.

Function WriteRangeToTable(InputRange As Range, TableName As String, SheetName As String)
    Dim MyTable As ListObject: Set MyTable = Worksheets(SheetName).ListObjects(TableName)
    If MyTable.DataBodyRange Is Nothing Then
        Set MyTable.InsertRowRange.Resize(InputRange.Rows.Count, InputRange.Columns.Count) = InputRange
    Else
        Set MyTable.DataBodyRange.Resize(InputRange.Rows.Count, InputRange.Columns.Count) = InputRange
    End If
End Function

CodePudding user response：

It's great that you have this small routine to do the copying plus using listobjects - thumb up!

But in this case you have to use PasteSpecial together with xlPasteFormulas