Thread: Macro to copy data , except where = sum appears

1. Macro to copy data , except where = sum appears

I have code to copy the Data in Col F (closing Balances) and to paste value these in Col C (Opening Balances) from row 3 onwards, except where the data contains an = sum formula. Where there is an =sum formula In Col F and Col C this must be left intact.

I have written code to copy the data from Col F to C , except not to copy the = sum( formula, but after running the macro, the opening bal is incorrect. for eg Corporate signage closing balance before running the macro was 11,477.00 -after running the macro the opening bal for corporate signage is 20,311.00 but should be 11,477

Code:
``` Sub Copy_Closing_Balances()
Sheets("sheet1").Select
Dim lr As Integer, Lr1 As Integer

lr = Range("F3").End(xlDown).Row

For I = 3 To lr
If Left(Range("F" & I).Formula, 4) = "=SUM" Then
Else
Range("C" & I).Value = Range("f" & I).Value
End If
Next I
Lr1 = Range("F16").End(xlDown).Row

For J = 3 To Lr1
If Left(Range("F" & J).Formula, 4) = "=SUM" Then
Else
Range("C" & J).Value = Range("f" & J).Value
End If
Next J
End Sub```

Attached please find my workbook before running the macro as well as after running the macro. It would be appreciated if someone could assist in resolving the problem

I have also posted on Mr Excel.com

http://www.mrexcel.com/forum/excel-q...copy-data.html

2. Howard,

When you get to the J loop, you are starting back at row 3 and duplicating the first loop therefore doubling your numbers.

Change
For J = 3 To Lr1
to
For J = 16 To Lr1