automating "paste - special - value"

[tumbler]

Fundamentally - I am trying to create functions that extract data from a library of other excel sheets, by using the concatenate feature - I am creating the text string of the cell reference in the other files, and then having to copy, paste special, value to get it to work, is there another way of fully automating this ?

Another problem I am having is that the cell does not calculate straight away, afer pasting value, I then need to re-enter the cell and press enter agin before the cell "works"

Please advise ?!

 

[BrianB]

Put these 2 lines in your code (assuming you have selected something first):-

'-----------------------------------------------------
Selection.PasteSpecial Paste:=xlValues
Calculate
'---------------------------------------------------------