Copying formulas

[redexodia]

hi all

been a while since i was last on here, been reading some useful books to expand my knowledge on vba.

I am looking for a work round for my current scenario.

I have created a "totals" page on multiple documents, example is using sumproducts, countifs and many more to collate the date from the whole workbook. Due to the workbooks been used daily and the complex formulas which are massive i have to copy paste info all the time to ensure performance is kept optimal.

I have created a template page in a seperate workbook, the vba code allows me to loop through each worksheet applying the template which is made up of copying and pasting over older formulas to display values only.

My question is can i use some syntax to copy a whole section of formulas.
at the moment i use the xlPasteAll to copy the page and then use

source = sheets("sheet2").range("A2:A10")
Dest = sheets("Totals").range("A2:A10")

Dest.value = source.formula

This is working for now but i would like to have an easier way as there are multiple locations of formulas (daily, monthly etc) It also needs to not reference back to the source workbook.

Any help would be appreciated

 

[Makrini]

Forgive me if I don't understand what you are meaning.

Not simply

xlPasteFormulas

?

 

[redexodia]

sorry i probably did waffle on a bit.

the xlpasteformulas seems to refer back to the original workbook eg.
=SUM([Book1.xls]Sheet1!$A$1:$A$10)

i need the formulas to simply say
=SUM(Sheet1!$A$1:$A$10)

when using the xlpasteformulas it refers back to the book1, so i have to use Source and destination with the value = formula.

eg
source = sheets("sheet2").range("A1:A10")
Dest = sheets("Totals").range("A1:A10")
Dest.value = source.formula

im wondering if there is any alternatives