Couple of options...
easiest to understand is copy and paste as values
Code:
range1.Copy
yourdestinationsheet.Range("A2").PasteSpecial (xlPasteValues)
more difficult to understand is to use an array
Code:
arr = range1.value
yourdestinationsheet.Range("A2").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
If you put the formula that's wanted into H2 and make it the active cell,
then go to the Immediate window, Ctrl + g, from the VBE, type in
? activecell.formulaR1C1
hit enter and you will see what Excel sees as being the formula in that cell
include H2 in the autofill range
Code:
Range("H2").FormulaR1C1 = "=xxx"
Range("H2").AutoFill Destination:=Range("H2:H" & lastrow)