Paste special values of a formula to a cell but NOT from a defined source cell

[Hai]

Right, this sounds very odd but my problem is as follows:

I have a script (thanks to MorganO -Owen from http://www.mrexcel.com/forum/showthread.php?t=324443) to build formulas that links to different cells of different workbooks and insert into destination cells of a consolidation workbook

So my destination cells will have formulas like:

='E:\My Work\Test\Data\[CCC.xlsx]Input'!$S$31

Everything works fine. But I don't want the destination cells to display the full formula, but the actual Value only

I thought of a way to build a script that first builds the formula to one cell then Copy Paste Special value of that Cell to my desired Cell.

But when the code runs The cursor moves back and forth all the time...

I hope you have a smarter way of solving my problem. Thanks so much!

 

[Hai]

Well, finally I got the logic of that Table Design Auto Fill Formula thing in XL07

If in the columns within that table, there's only my formula linking to closed workbooks, then the table will auto fill all the blank cells of that column with the formula

. So I created an extra row below the header row, and put a static value there. The auto fill feature will not be activated when links a built into all the rows below that row!

Thanks for your inspirational help, lasw10 and hope you'll be able to get XL07 soon!