VBA Copy formulas and paste as values

[kanishkgarg]

Hi,

I have got a fairly simple problem. The following is the part of my code i am having problem with:

s2.Range("d3").Select
ActiveCell.Formula2R1C1 = "=SUMPRODUCT(IF('" & s1.Name & "'!R3C5:R50000C5='" & s2.Name & "'!RC1, '" & s1.Name & "'! R3C20:R50000C20*'" & s1.Name & "'!R3C28:R50000C28))"

s2.Range("d3:d" & Range("A" & Rows.Count).End(xlUp).Row).FillDown
   


s2.Range(Range("B3:D3"), Range("A3").End(xlDown)).Select
Selection.Copy
s2.Range("B3").PasteSpecial xlPasteValues

Data from B3 to D is full of formulas. I would like to copy and paste it as values. When I use this code for some reason, it pastes the values of three cells (B3:D3) throughout the 3 columns. I need it to paste the values as is.

Any help is greatly appreciated!

 

[gordsky]

where are you trying to paste?? what is in column A
your code above selects all cells from A3 to D(lastrow in a), your then asking it to copy that selection and paste it to b3 which will paste the range and effectively offset the range by 1 column

 

[MARK858]

Maybe...

With s2.Range("B3:D" & s2.Range("A" & Rows.count).End(xlUp).Row)
    .Value = .Value
End With