Macro to Copy Data

[howard]

I have the following Data Below

I have code to copy the Data in Col F and to paste value these in Col C from row 3 onwards, except where the data contains = sum formula (this must be left intact

When running the formula the the opening bal should equal the closing bal before the macro was run

 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

This is what the openiing balances look like after running the macro

your assistance in resolving this is miost appreciated

Excel Workbook
	A	B	C	D	E	F
1
2	Description	Account Number	Opening Balance Cost	Purchases	Disposals	Closing Balance Cost
3	Corporate Signage	4551370	20,311.00	8,834.00	0.00	29,145.00
4	Furniture and Fittings	4551320	10,270.38	4,754.20	0.00	15,024.58
5	Generators	4551350	0.00	0.00	0.00	0.00
6	Office Equipment	4551330	2,101.65	0.00	0.00	2,101.65
7	Plant and Machinery	4551310	10,875.02	3,940.27	0.00	14,815.29
8	Computer Equipment	4551340	0.00	0.00	0.00	0.00
9	Small Tools	4551380	0.00	0.00	0.00	0.00
10
11	Total		43,558.05	17,528.47	0.00	61,086.52
12
13
14
15	Description	Account Number	Acc Depr	Current Year Depr	Disposals	Acc Depr
16	Corporate Signage	4551470	3,133.14	490.14	0.00	3,623.29
17	Furniture and Fittings	4551420	1,158.16	396.18	0.00	1,554.34
18	Generators	4551450	0.00	0.00	0.00	0.00
19	Office Equipment	4551430	2,101.65	0.00	0.00	2,101.65
20	Plant and Machinery	4551410	2,599.68	613.87	0.00	3,213.56
21	Computer Equipment	4551440	0.00	0.00	0.00	0.00
22	Small Tools	4551480	0.00	0.00	0.00	0.00
23
24	Total		8,992.64	1,500.20	0.00	10,492.84
25
26
27
Sheet1

 

[WaterGypsy]

What is the problem though?

 

[howard]

Sample Data before running Macro

Excel 2012
	A	B	C	D	E	F
1			Per Asset Register	Updated on: 17/11/2014 18:16
2	Description	Account Number	Opening Balance Cost	Purchases	Disposals	Closing Balance Cost
3	Corporate Signage	4551370	2,643.00	8,834.00	0.00	11,477.00
4	Furniture and Fittings	4551320	761.98	4,754.20	0.00	5,516.18
5	Generators	4551350	0.00	0.00	0.00	0.00
6	Office Equipment	4551330	2,101.65	0.00	0.00	2,101.65
7	Plant and Machinery	4551310	2,994.49	3,940.27	0.00	6,934.76
8	Computer Equipment	4551340	0.00	0.00	0.00	0.00
9	Small Tools	4551380	0.00	0.00	0.00	0.00
10
11	Total		8,501.12	17,528.47	0.00	26,029.59
12
13
14	0
15	Description	Account Number	Acc Depr	Current Year Depr	Disposals	Acc Depr
16	Corporate Signage	4551470	2,643.00	490.14	0.00	3,133.14
17	Furniture and Fittings	4551420	761.98	396.18	0.00	1,158.16
18	Generators	4551450	0.00	0.00	0.00	0.00
19	Office Equipment	4551430	2,101.65	0.00	0.00	2,101.65
20	Plant and Machinery	4551410	1,985.81	613.87	0.00	2,599.68
21	Computer Equipment	4551440	0.00	0.00	0.00	0.00
22	Small Tools	4551480	0.00	0.00	0.00	0.00
23
24	Total		7,492.44	1,500.20	0.00	8,992.64
25
26
27
Sheet1

Sample Data after Running Macro

Excel 2012
	A	B	C	D	E	F	G
1			Per Asset Register	Updated on: 17/11/2014 18:19
2	Description	Account Number	Opening Balance Cost	Purchases	Disposals	Closing Balance Cost
3	Corporate Signage	4551370	20,311.00	8,834.00	0.00	29,145.00
4	Furniture and Fittings	4551320	10,270.38	4,754.20	0.00	15,024.58
5	Generators	4551350	0.00	0.00	0.00	0.00
6	Office Equipment	4551330	2,101.65	0.00	0.00	2,101.65
7	Plant and Machinery	4551310	10,875.02	3,940.27	0.00	14,815.29
8	Computer Equipment	4551340	0.00	0.00	0.00	0.00
9	Small Tools	4551380	0.00	0.00	0.00	0.00
10
11	Total		43,558.05	17,528.47	0.00	61,086.52
12
13
14	0
15	Description	Account Number	Acc Depr	Current Year Depr	Disposals	Acc Depr
16	Corporate Signage	4551470	3,133.14	490.14	0.00	3,623.29
17	Furniture and Fittings	4551420	1,158.16	396.18	0.00	1,554.34
18	Generators	4551450	0.00	0.00	0.00	0.00
19	Office Equipment	4551430	2,101.65	0.00	0.00	2,101.65
20	Plant and Machinery	4551410	2,599.68	613.87	0.00	3,213.56
21	Computer Equipment	4551440	0.00	0.00	0.00	0.00
22	Small Tools	4551480	0.00	0.00	0.00	0.00
23
24	Total		8,992.64	1,500.20	0.00	10,492.84
25
26
Sheet1

Look at row 3 Corporate signage-the closing balance was R 11,477.00 -after running the macro the opening bal for corporate signage should be R 11477. but instead the opening bal shows is 20311

It would be appreciated if you could assist in resolving this

 

[howard]

I have also posted on Windowssecets

Macro to copy data , except where = sum appears | Windows Secrets Lounge

 

[WaterGypsy]

Your macro effectively runs twice ..... you have 2 for loops that do essentially the same and so the first loop copies the closing balance to the opening balance ... the closing balance is then updated by its own formula and then the 2nd loop copies it to the opening balance again.

Check the first row numbers and you'll see what I mean