#REF when copying the result of a formula using VBA

[eDarren]

I have a few different sheets within one workbook, and i'm intending to have user defined data for cable calculations, when the data is input the user hits the calculate macro and VBA moves a bunch of things around to populate a Summary of the Calculations. I'm using some standard formulas within excel (for example =sum(I6/B1)) to perform some basic calculations. I then want to copy and paste the result of said formula into a seperate sheet which I intend to be a Summary of all the Calculations, making it more human readable and less jargon. My issue is, that when I do a copy/paste in VBA, I end up with a #REF because it's copied the formula from the previous sheet, but the new sheet doesn't have any of the values in the boxes that it's looking for....

My question is, how do I copy the result of a formula (=sum(I6/B1) into a seperate sheet set? I want this so be performed via a macro, so somebody clicks a "calculate" button when they are ready to, rather than automatically calculating using standard excel formulas.

 

[offthelip]

I do it by using a variant array as the intermediate, which defaults to values only

Sub test()
Var = Worksheets("Sheet2").Range("B3:D5")
Worksheets("Sheet3").Range("B3:D5") = Var

Note you can do it using copyvalues but actually it takes rather more typing that using a varaint array

 

[eDarren]

I do it by using a variant array as the intermediate, which defaults to values only

Sub test()
Var = Worksheets("Sheet2").Range("B3:D5")
Worksheets("Sheet3").Range("B3:D5") = Var

Note you can do it using copyvalues but actually it takes rather more typing that using a varaint array

Legend, that did it!