Re-calculate cell everytime it is changed

I have a spreadsheet where sheet("summary").Cells("K4") = sheet("component").Cells("G7").

This is written as a formula, e.g. =SUM('Component'!G7).

I am trying to achieve the same through VBA. I can get the summary cell to copy the others. but it just copies it the once when it is created. How can I make this re-calculate everytime it is changed?

Please Login or Register  to view this content.

I might be wrong, but I think it is because you have linked your cells only at the time the vba macro runs?

Please Login or Register  to view this content.

My evaluation of what your code is doing it that you are basically starting at K4 on your summary sheet, and copying to that the value from G7 of the 'component' sheet.
On the next loop the value is copied into K5 from G7 of THAT component, and so on, moving row by row down the summary sheet?

If this is the case then the reason it only works once is because you are assigning the VALUE at the moment the macro is run, where as if you want to update all the time you need to make the cell contain a formuala.
I would adjust your code as follows using the R1C1 format of addressing Rows and Columns. (I have commented your lines of code so that you can hopefully see which lines my new code is related to)

Please Login or Register  to view this content.

Note the single quotes included in the 'formula' line. These will cause the name of the worksheet to be enclosed in single quotes which allows for the fact that component might include an embedded space character.

EDITED I got my rows and columns the wrong way around. Now corrected