I was recently asked this question: Is there a way in Excel to write a macro to copy a cell's numeric value only and not the formula? Basically, can you use VBA to paste only the value, like the Paste Special feature in the Values Only menu? The answer is yes!
To do a simple Paste operation you might write this line of code:
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("A2")
Instead of '.Paste', use '.PasteSpecial." Try it with this line:
Worksheets("Positions").Range("A2").PasteSpecial xlPasteValues
If you still have questions then see the Microsoft Excel VBA help file for the use of the PasteSpecial method. It's pretty straightforward.
Another suggestion, instead of using code like this:
Set rng6 = .Range("A3").End(xlDown).Offset(0, 41)
rng6.Copy
ActiveSheet.Paste Destination:=Worksheets("Positions").Range("A2")
you could use something like this:
Worksheets("Positions").Range("W2").value = .Range("A3").End(xlDown).Offset(0, 41).value
This new code will accomplish the same thing as the original but it will also allow your code to run more quickly by avoiding the use of copy and paste althogether.
