In Workbook1 I have the following formula in Cell AG9 in Worksheet "New Table1"
=COUNTIF('New Table2'!B:B;"D")
It's supposed to count all the D's in "New Table2" Column B. Using VBA I want to copy the whole Worksheet "New Table1" into a different Workbook(Workbook2), but when I do so, the copied formula in Workbook2 changes to
=COUNTIF('C:\Users\a.hopf\Desktop\[Workbook1.xlsx]New Table2 '!B:B;"D")
How can I prevent the formula from referencing to the original workbook? The Formula in Workbook2 should also reference to New Table2 in Workbook2. I tried using $ to create a absolute reference, but =COUNTIF($'New Table2'!$B:$B;"D")
doesn't work.
I know I could write the formula into Workbook2 using VBA , but I would prefer to copy it together with the worksheet from Workbook1.