Good evening,
I already have a working macro which separates rows when a total of 45 is met.
I then run my next macro with inserts a total in bold blue:
Sub format()
Dim Rng As Range
For Each Rng In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
With Rng.Offset(Rng.Count).Resize(1)
.Formula = "=sum(" & Rng.Address & ")"
.Font.Bold = True
.Interior.Color = 14470546
End With
Next Rng
End Sub
Is it possible to add to this macro so that it copies the data in A3, B3, C3 and inserts them into the blank row then moving the totals into column D.
This is what I have:
This is what I want:
I am hoping that with titles inserted I can re pivot to summarize the data again? Or is this not the way to do it?
I have split my data into singles, so I could separate into loads as there will often be split loads. Now I need to summarise the ones again to produce a plan that can be followed.
Any help greatly appreciated.
Sara
I already have a working macro which separates rows when a total of 45 is met.
I then run my next macro with inserts a total in bold blue:
Sub format()
Dim Rng As Range
For Each Rng In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
With Rng.Offset(Rng.Count).Resize(1)
.Formula = "=sum(" & Rng.Address & ")"
.Font.Bold = True
.Interior.Color = 14470546
End With
Next Rng
End Sub
Is it possible to add to this macro so that it copies the data in A3, B3, C3 and inserts them into the blank row then moving the totals into column D.
This is what I have:
NEW WAVE FILE TRUNKS.xlsm | |||||
---|---|---|---|---|---|
A | B | C | |||
1 | |||||
2 | Store No. | Store Name | Pallets | ||
3 | 114 | Jersey | 1 | ||
4 | 114 | Jersey | 1 | ||
5 | 114 | Jersey | 1 | ||
6 | 865 | Broadstairs | 1 | ||
7 | 865 | Broadstairs | 1 | ||
8 | 865 | Broadstairs | 1 | ||
9 | 865 | Broadstairs | 1 | ||
10 | 800 | Canterbury | 1 | ||
11 | 800 | Canterbury | 1 | ||
12 | 800 | Canterbury | 1 | ||
13 | 800 | Canterbury | 1 | ||
14 | 800 | Canterbury | 1 | ||
15 | 800 | Canterbury | 1 | ||
16 | 800 | Canterbury | 1 | ||
17 | 800 | Canterbury | 1 | ||
18 | 800 | Canterbury | 1 | ||
19 | 800 | Canterbury | 1 | ||
20 | 800 | Canterbury | 1 | ||
21 | 265 | Ashford | 1 | ||
22 | 265 | Ashford | 1 | ||
23 | 265 | Ashford | 1 | ||
24 | 265 | Ashford | 1 | ||
25 | 265 | Ashford | 1 | ||
26 | 265 | Ashford | 1 | ||
27 | 965 | Sittingbourne | 1 | ||
28 | 965 | Sittingbourne | 1 | ||
29 | 965 | Sittingbourne | 1 | ||
30 | 965 | Sittingbourne | 1 | ||
31 | 965 | Sittingbourne | 1 | ||
32 | 965 | Sittingbourne | 1 | ||
33 | 475 | Hastings | 1 | ||
34 | 475 | Hastings | 1 | ||
35 | 475 | Hastings | 1 | ||
36 | 475 | Hastings | 1 | ||
37 | 475 | Hastings | 1 | ||
38 | 840 | Eastbourne | 1 | ||
39 | 840 | Eastbourne | 1 | ||
40 | 840 | Eastbourne | 1 | ||
41 | 840 | Eastbourne | 1 | ||
42 | 840 | Eastbourne | 1 | ||
43 | 840 | Eastbourne | 1 | ||
44 | 840 | Eastbourne | 1 | ||
45 | 840 | Eastbourne | 1 | ||
46 | 840 | Eastbourne | 1 | ||
47 | 840 | Eastbourne | 1 | ||
48 | 45 | ||||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C48 | C48 | =SUM($C$2:$C$47) |
This is what I want:
NEW WAVE FILE TRUNKS.xlsm | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | ||||||
2 | Store No. | Store Name | Pallets | |||
3 | 114 | Jersey | 1 | |||
4 | 114 | Jersey | 1 | |||
5 | 114 | Jersey | 1 | |||
6 | 865 | Broadstairs | 1 | |||
7 | 865 | Broadstairs | 1 | |||
8 | 865 | Broadstairs | 1 | |||
9 | 865 | Broadstairs | 1 | |||
10 | 800 | Canterbury | 1 | |||
11 | 800 | Canterbury | 1 | |||
12 | 800 | Canterbury | 1 | |||
13 | 800 | Canterbury | 1 | |||
14 | 800 | Canterbury | 1 | |||
15 | 800 | Canterbury | 1 | |||
16 | 800 | Canterbury | 1 | |||
17 | 800 | Canterbury | 1 | |||
18 | 800 | Canterbury | 1 | |||
19 | 800 | Canterbury | 1 | |||
20 | 800 | Canterbury | 1 | |||
21 | 265 | Ashford | 1 | |||
22 | 265 | Ashford | 1 | |||
23 | 265 | Ashford | 1 | |||
24 | 265 | Ashford | 1 | |||
25 | 265 | Ashford | 1 | |||
26 | 265 | Ashford | 1 | |||
27 | 965 | Sittingbourne | 1 | |||
28 | 965 | Sittingbourne | 1 | |||
29 | 965 | Sittingbourne | 1 | |||
30 | 965 | Sittingbourne | 1 | |||
31 | 965 | Sittingbourne | 1 | |||
32 | 965 | Sittingbourne | 1 | |||
33 | 475 | Hastings | 1 | |||
34 | 475 | Hastings | 1 | |||
35 | 475 | Hastings | 1 | |||
36 | 475 | Hastings | 1 | |||
37 | 475 | Hastings | 1 | |||
38 | 840 | Eastbourne | 1 | |||
39 | 840 | Eastbourne | 1 | |||
40 | 840 | Eastbourne | 1 | |||
41 | 840 | Eastbourne | 1 | |||
42 | 840 | Eastbourne | 1 | |||
43 | 840 | Eastbourne | 1 | |||
44 | 840 | Eastbourne | 1 | |||
45 | 840 | Eastbourne | 1 | |||
46 | 840 | Eastbourne | 1 | |||
47 | 840 | Eastbourne | 1 | |||
48 | Store No. | Store Name | Pallets | 45 | ||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D48 | D48 | =SUM($C$2:$C$47) |
I am hoping that with titles inserted I can re pivot to summarize the data again? Or is this not the way to do it?
I have split my data into singles, so I could separate into loads as there will often be split loads. Now I need to summarise the ones again to produce a plan that can be followed.
Any help greatly appreciated.
Sara