I am trying to create exact copy of excel sheet in another file. I have tried using the following three methods
- Cell References – Hardcoding Cell Reference in other file i.e.
$A$1 VBA- Using Data Connections of
PowerQuery
However, each has their own limitations
- Cell References – If I add or delete a column or row, the references do not work
VBA– both the files need to be open which is not possible in our situation- Power Query – It does active data integrity but fails completely in case of formatting and do not copy the
commentscreated in thesource file
How do I go about this?
I have a deadline to meet, any guidance is greatly appreciated.
Thanks in advance.