CODEWITHSUNDEEP
vbaexcel
geek2000
geek2000

Reputation: 481

Copy values to a sheet containing formulas without formulas being removed

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

Upvotes: 0

Views: 85

Answers (1)

JvdV
JvdV

Reputation: 75840

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

Upvotes: 1

Related Questions