"PasteSpecial xlPasteFormulas" with relative reference with destination ListObject

[bmont]

My goal is to copy-paste formulas from tableSource (a ListObject) to tableDest (also a ListObject) and to have those pasted formulas refer to tableDest, not to tableSource, which is what I get when I do a little something like this:

ListObjSource.ListRows(1).Range.Copy
rangeDestination.PasteSpecial xlPasteFormula

...resulting in formulas within tableDest referring to not their native ListObject.

One could use Replace to edit the strings but is there a smarter way?

Thanks in advance.

 

[pgc01]

Hi

When you post code please post the declarations and initialisations of the objects mentioned.
In this case it would also help to post the structure of the tables and the logic of what you're doing.

I'll assume that
. rangeDestination is a range inside a second table
. this second table has fields with the same name as the first table so that the same formula can work on both.

With these assumptions the solution is to copy the formula directly.

This is an example that copies the formula in the second column of the first table into the second column of the second table.

Test:

Sub Macro1()
Dim ListObjSource As ListObject, ListObjDest As ListObject

Set ListObjSource = ActiveSheet.ListObjects(1)
Set ListObjDest = ActiveSheet.ListObjects(2)

' copy the formula on the second column
ListObjDest.ListRows(1).Range(2).Formula = ListObjSource.ListRows(1).Range(2).Formula

End Sub

 

[bmont]

Thank you, pgc01. This works perfectly and has me thinking differently now about copy-paste vs your approach. Awesome.

 

[pgc01]

I'm glad it helped.