Hi,
I have a lot of Data from many Sheets and I would like to use VBA to auto consolidate the latest Month Data into one Summary Sheet.
Below are some sample data:
<tbody>
</tbody><colgroup><col><col><col><col><col><col span="6"></colgroup>
<tbody>
</tbody><colgroup><col><col><col><col><col><col span="6"></colgroup>
<tbody>
</tbody><colgroup><col><col><col><col><col><col span="12"></colgroup>
Summary:
<tbody>
</tbody><colgroup><col><col></colgroup>
Looking forward to hear the good news soon.
Thanks in advance for the helps.
I have a lot of Data from many Sheets and I would like to use VBA to auto consolidate the latest Month Data into one Summary Sheet.
Below are some sample data:
Sheet1 | May-17 | Jun-17 | Jul-17 | Aug-17 | Sep-17 | |||||
PARTs | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price |
JC01 | 160.15 | $1,263.58 | 280.00 | $3,514.00 | 32.00 | $401.60 | 16.00 | $126.24 | 29.00 | $363.95 |
JC02 | 71.00 | $560.19 | 329.98 | $4,141.25 | 28.00 | $351.40 | 18.00 | $142.02 | 20.50 | $257.28 |
JC03 | 0.00 | $0.00 | 60.00 | $753.00 | 31.00 | $389.05 | 0.00 | $0.00 | 3.00 | $37.65 |
JC04 | 0.00 | $0.00 | 18.00 | $225.90 | 70.00 | $878.50 | 28.00 | $220.92 | 8.00 | $100.40 |
JC05 | 20.00 | $157.80 | 112.00 | $1,405.60 | 12.00 | $150.60 | 6.00 | $47.34 | 2.00 | $25.10 |
JC06 | 11.00 | $86.79 | $0.00 | 17.00 | $213.35 | 0.00 | $0.00 | 0.00 | $0.00 | |
JC07 | 55.00 | $433.95 | $0.00 | 20.00 | $251.00 | 0.00 | $0.00 | 2.00 | $25.10 | |
JC08 | 0.00 | $0.00 | $0.00 | 21.00 | $263.55 | 0.00 | $0.00 | 10.00 | $125.50 |
<tbody>
</tbody><colgroup><col><col><col><col><col><col span="6"></colgroup>
Sheet2 | May-17 | Jun-17 | Jul-17 | Aug-17 | Sep-17 | |||||
PARTs | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price |
99D01 | 33.00 | $214.50 | 30.00 | $267.00 | 34.00 | $302.60 | 38.00 | $247.00 | 7.00 | $45.50 |
99D02 | 31.00 | $201.50 | 30.00 | $267.00 | 32.00 | $284.80 | 2.00 | $13.00 | 7.00 | $45.50 |
99D03 | 32.00 | $208.00 | 27.00 | $240.30 | 35.00 | $311.50 | 5.00 | $32.50 | 34.00 | $221.00 |
99D04 | 33.00 | $214.50 | 29.00 | $258.10 | 23.00 | $204.70 | 13.00 | $84.50 | 32.00 | $208.00 |
99D05 | 32.00 | $208.00 | 29.00 | $258.10 | 73.00 | $649.70 | 15.00 | $97.50 | 36.00 | $234.00 |
99D06 | 32.00 | $208.00 | 29.00 | $258.10 | 5.00 | $44.50 | 8.00 | $52.00 | 42.00 | $273.00 |
99D07 | 36.00 | $234.00 | 30.00 | $267.00 | 63.00 | $560.70 | 6.00 | $39.00 | 5.00 | $32.50 |
99D08 | 33.00 | $214.50 | 30.00 | $267.00 | 48.00 | $427.20 | 6.00 | $39.00 | 2.00 | $13.00 |
99D09 | 18.00 | $117.00 | 30.00 | $267.00 | 75.00 | $667.50 | 53.00 | $344.50 | 34.00 | $221.00 |
99D10 | 39.00 | $253.50 | 29.00 | $258.10 | 50.00 | $445.00 | 47.00 | $305.50 | 34.00 | $221.00 |
99D11 | 32.00 | $208.00 | 30.00 | $267.00 | 8.00 | $71.20 | 8.00 | $52.00 | 45.00 | $292.50 |
99D12 | 39.00 | $253.50 | 35.00 | $311.50 | 9.00 | $80.10 | 6.00 | $39.00 | 6.00 | $39.00 |
99D13 | 22.00 | $143.00 | 22.00 | $195.80 | 17.00 | $151.30 | 10.00 | $65.00 | 41.00 | $266.50 |
99D14 | 25.00 | $162.50 | 29.00 | $258.10 | 50.00 | $445.00 | 52.00 | $338.00 | 8.00 | $52.00 |
<tbody>
</tbody><colgroup><col><col><col><col><col><col span="6"></colgroup>
Sheet3 | Feb-17 | Mar-17 | Apr-17 | May-17 | Jun-17 | Jul-17 | Aug-17 | Sep-17 | ||||||||
PARTs | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price | Tatal Quantity | price |
HV01 | 30.00 | $173.67 | 6.00 | $58.45 | 23.00 | $224.07 | 28.00 | $314.86 | 36.00 | $183.60 | 15.00 | $76.50 | 27.00 | $300.24 | 24.00 | $196.21 |
HV02 | 28.00 | $162.09 | 27.00 | $263.03 | 5.00 | $48.71 | 25.00 | $281.13 | 0.00 | $0.00 | 32.00 | $163.20 | 27.00 | $300.24 | 26.00 | $212.56 |
HV03 | 41.00 | $237.35 | 60.00 | $584.52 | 28.00 | $272.78 | 14.00 | $157.43 | 35.00 | $178.50 | 32.00 | $163.20 | 19.00 | $211.28 | 30.00 | $245.26 |
HV04 | 12.00 | $69.47 | 23.00 | $224.07 | 2.00 | $19.48 | 25.00 | $281.13 | 22.00 | $112.20 | 18.00 | $91.80 | 30.00 | $333.60 | 35.00 | $286.14 |
HV05 | 27.00 | $156.30 | 16.00 | $155.87 | 25.00 | $243.55 | 23.00 | $258.64 | 20.00 | $102.00 | 16.00 | $81.60 | 0.00 | $0.00 | 29.00 | $237.09 |
HV06 | 26.00 | $150.51 | 13.00 | $126.65 | 10.00 | $97.42 | 20.00 | $224.90 | 0.00 | $0.00 | 16.00 | $81.60 | 0.00 | $0.00 | 27.00 | $220.74 |
HV07 | 22.00 | $127.36 | 16.00 | $155.87 | 19.00 | $185.10 | 2.00 | $22.49 | 27.00 | $137.70 | 24.00 | $122.40 | 17.00 | $189.04 | 14.00 | $114.46 |
HV08 | 12.00 | $69.47 | 8.00 | $77.94 | 4.00 | $38.97 | 0.00 | $0.00 | 0.00 | $0.00 | 20.00 | $102.00 | 10.00 | $111.20 | 9.00 | $73.58 |
HV09 | 25.00 | $144.73 | 12.00 | $116.90 | 23.00 | $224.07 | 0.00 | $0.00 | 26.00 | $132.60 | 24.00 | $122.40 | 28.00 | $311.36 | 12.00 | $98.10 |
<tbody>
</tbody><colgroup><col><col><col><col><col><col span="12"></colgroup>
Summary:
Sep-17 | |
PARTs | Tatal Quantity |
JC01 | 29.00 |
JC02 | 20.50 |
JC03 | 3.00 |
JC04 | 8.00 |
JC05 | 2.00 |
JC06 | 0.00 |
JC07 | 2.00 |
JC08 | 10.00 |
99D01 | 7.00 |
99D02 | 7.00 |
99D03 | 34.00 |
99D04 | 32.00 |
99D05 | 36.00 |
99D06 | 42.00 |
99D07 | 5.00 |
99D08 | 2.00 |
99D09 | 34.00 |
99D10 | 34.00 |
99D11 | 45.00 |
99D12 | 6.00 |
99D13 | 41.00 |
99D14 | 8.00 |
HV01 | 24.00 |
HV02 | 26.00 |
HV03 | 30.00 |
HV04 | 35.00 |
HV05 | 29.00 |
HV06 | 27.00 |
HV07 | 14.00 |
HV08 | 9.00 |
HV09 | 12.00 |
<tbody>
</tbody><colgroup><col><col></colgroup>
Looking forward to hear the good news soon.
Thanks in advance for the helps.