vba to copy range containing formulas and paste back values in the same range

[rjheibel]

I am trying to copy a range with formulas and paste back values only in the same range. I tried the statement below, but it replaces formulas with blanks. Attempting to do this action without actually selecting that sheet if possible. What would be the best way to accomplish this?

Worksheets("Previous Week Performance").Range("A4:AE1050").Value = Worksheets("Previous Week Performance").Range("A4:AE1050").Value

Thanks!

 

[Jeffrey Mahoney]

That statement looks correct to value over formulas. If the formulas result to a null value such as "", then you will get null string cells.

Are you trying to say that the formulas result to numbers and that statement turns them into blanks?

 

[rjheibel]

Your right. That statement is working. I changed to that after the statement below did not work. Is there another way to write this statement within the With Statement? As written, when the second statement runs, it clears everything out. seems like it doesn't want to copy formulas back as values.

With Worksheets("Previous Week Performance")
.Range("A4:AE1050").Formula = .Range("A4:AE4").Formula
.Range("A4:AE1050").Value = .Range("A4:AE4").Value
End With

 

[RoryA]

You got the last range wrong in the penultimate line:

.Range("A4:AE1050").Value = .Range("A4:AE1050").Value