VBA - find Data from latest Date and copy into one Sheet

[iris1007]

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:　

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.

 

[My Aswer Is This]

Try this:

Sub Consolidate_Data()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Dim LastColumn As Long
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Summary" Then
            LastColumn = Sheets(i).Cells(2, Columns.Count).End(xlToLeft).Column - 1
            Sheets("Summary").Range("A2").Value = Sheets(i).Range("A2").Value
            Lastrowa = Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
            Lastrow = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
            With Sheets(i)
                Sheets("Summary").Range("B1").Value = Sheets(i).Cells(1, LastColumn).Value
                Sheets("Summary").Range("B2").Value = Sheets(i).Cells(2, LastColumn).Value
                .Range("A3:A" & Lastrowa).Copy Sheets("Summary").Range("A" & Lastrow)
                .Range(.Cells(3, LastColumn), .Cells(Lastrowa, LastColumn)).Copy Sheets("Summary").Range("B" & Lastrow)
            End With
        
        End If
    Next
Application.ScreenUpdating = True
End Sub

 

[iris1007]

Perfect. Thank you so much for the helps.