Copy values to a sheet containing formulas without formulas being removed

Question

I have two Worksheets: Sheet1 and Sheet2. Sheet1 has raw data (without any formulas), Sheet2 has formulas in some cells. Is it possible to copy values from Sheet1 to Sheet2 without formulas being removed from Sheet2?

I am using the following code to copy values from sheet1 to sheet2. After values are copied, formulas in Sheet2 get removed.

i = 2

With Sheets(1)
   'loop column A 
   For Each Cell In .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
      .Rows(Cell.Row).Copy Destination:=Sheets(2).Range("A" & i)
      i = i + 1
   Next Cell
End With

Formulas shouldn't be the problem. Overwriting seems like being the problem. If you don't want to overwrite cells with formulas add a test that the cell being written to doesn't have a formula with HasFormula or Left(cell.value,1) = "=" — QHarr
You are also copying an entire row so are you expecting to test whether any cell within the destination row has a formula? — QHarr
Sheet2 is always empty, only contains a bunch of formulas. It is supposed to act like a calculator. It gets input and produces output, and then get cleaned for the new set of values. I want the formulas to stay there. But the code removes formulas. — geek2000
1) Sheet 2 is therefore not empty (sorry to be pedantic) 2) Why not simply choose to output to a different range where there are no formulas? — QHarr
1) Sorry I meant there are no values in it, only formulas. 2) It just doesn't look very professional if it jumps to some random place. ;-) I want everything to be in-place. But if there is no way to do it... I should go with your suggestion. — geek2000

JvdV JvdV · Accepted Answer · 2018-05-30T10:12:47

This code will not copypaste the whole row but will loop through each cell in your row on the raw data and only copypaste the value when in the same cell on your other sheet no formula excists:

Sub TestForFormula()

Dim X As Long
Dim CL As Range, RNG As Range

For X = 2 To Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    Set RNG = ActiveSheet.Range(Cells(X, 1), Cells(X, ActiveSheet.Cells(X, Columns.Count).End(xlToLeft).Column))
    For Each CL In RNG
        If Sheets(2).Cells(X, CL.Column).HasFormula = False Then
            Sheets(2).Cells(X, CL.Column).Value = Sheets(1).Cells(X, CL.Column).Value
        End If
    Next CL
Next X

End Sub

Copy values to a sheet containing formulas without formulas being removed

1 Answers