Excel macro copy values from one row to another without overwriting formulas in the target row

Question

I am trying to create a macro that copies data from the current month row to the bottom. The current month row has links in the cells, so when it is refreshed,the date and the data will change for a new month (sept--16), but before it is refreshed, copy the data to the matching month below. The balance column has a formula in each cell (total - amount). How do i copy down just the values, without overwriting the formula in the balance cell.

here is the code i have

Sub CopyData()
Dim compareValue As String
Dim comparingValue As String
Dim i As Long
compareValue = Cells(2, 2).Value

For i = 3 To Rows.Count
    comparingValue = Cells(i, 2).Value
    If compareValue = comparingValue Then
        Sheets("Sheet1").Range("C2:J2").Copy
        lRow = Range("C" & Rows.Count).End(xlUp).Row
         Range("C" & lRow + 1, "J" & lRow + 1).PasteSpecial xlPasteValues
    End If
Next i
End Sub

Unknown Unknown · Accepted Answer · 2016-09-06T14:26:09

Use a direct value transfer.

with worksheets("Sheet1").cells(i, "C").resize(1, 8)
    worksheets("another sheet").cells(rows.count, "C").end(xlup).offset(1, 0).resize(1, 8) = .value
end with

Excel macro copy values from one row to another without overwriting formulas in the target row

1 Answers