Pasting formulas without copying file ref.

[Guest]

Rather than rewrite 40 + formulas to the R1C1 format, I am trying to simply have the macro open a file who's size does not matter, and copy the many worksheets of formulas to the actual file, let it calculate, and then paste values, the only problem is that of course when I pase the formulas, they cease to be realtive, and now have the first file's referenced in all the formulas. Let me know if there is a workaround either through VBA code or even something simple built into paste special or something along those lines. - I could just find / replace everything, but considering there are 4000+ formulas, many of them with multiple references, I'd like not to slow the macro down by doing so.

 

[Dave Peterson]

Maybe you could just assign the formula to the cell:

dim fwks as worksheet
dim twks as worksheet

set fwks = workbooks("book1.xls").worksheets("sheet1")
set twks = workbooks("book2.xls").worksheets("sheet22")

twks.range("a1:a99").formular1c1 = fwks.range("a1").formular1c1

(99 cells get the same formula as one cell)

(watch for typos--I composed it in the email.)