auto filtered from a column at second sheet but get the value from each row in this column seperately

[Ismailfares]

Hi Guys,
I appreciate ur help ,

in sheet 1, i have many columns from A to AA and i need to write a code that get unique values from column D (years) at first sheet to the new sheet at A2 location then for each unique values i need to do filter for each value in Column D at first sheet again and when i do filter for year as ex.2000 i need to sum all rows in column M and copy the result (at last row) and paste it in second sheet beside unique values (B2)
so i need to this operation continuously till the column of unique values at second sheet finished then the program stopped
i do not know the years it is not specific , i mean i have different excel sheet and i do not want to write a code with specific year bcs it will not work in another worksheet
how can i do auto filters from column but for each row.
(Result at the end :it should be two columns at second sheet ,A2 has the unique values (years) ,B2 has values of sum column M at first sheet )

i need it necessary .Thansk for help

 

[DanteAmor]

Welcome to the board!

The following is assuming that in the D column you have years, if you have dates I would have to make a small adjustment in the code.

Sub Sum_Values()
  Dim a() As Variant, dic As Object, i As Long
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Sheet1").Range("D2", Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    dic(a(i, 1)) = dic(a(i, 1)) + a(i, 10)
  Next
  Sheets("Sheet2").Range("A2").Resize(dic.Count, 1).Value = Application.Transpose(dic.keys)
  Sheets("Sheet2").Range("B2").Resize(dic.Count, 1).Value = Application.Transpose(dic.items)
End Sub

If you have dates, change this line
dic(a(i, 1)) = dic(a(i, 1)) + a(i, 10)

For this:
dic(Year(a(i, 1))) = dic(Year(a(i, 1))) + a(i, 10)

 

[Ismailfares]

Thanks alot,i spent the whole day try to find solution ,
Please i want to add the header of this also the same as wheet 1 and i did some code for another table and it should add at the same sheet 2

but i want the second table starts after 5 row from the end of the first table ,so how can i do that .

Thanks alot for help

 

[DanteAmor]

I do not understand what do you need.

If you want to copy the header of sheet1 to sheet2, just add this line:
Sheets("Sheet1").rows(1).copy Sheets("Sheet2").range("A1")
But I don't know where you have the header, for example if it is row 4, then change the 1 to 4.
Sheets("Sheet1").rows(4).copy Sheets("Sheet2").range("A4")

but i want the second table starts after 5 row from the end of the first table ,so how can i do that .

For that, maybe it's this:

Sub Sum_Values()
  Dim a() As Variant, dic As Object, i As Long
  Set dic = CreateObject("Scripting.Dictionary")
  a = Sheets("Sheet1").Range("D2", Sheets("Sheet1").Range("M" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    dic(a(i, 1)) = dic(a(i, 1)) + a(i, 10)
  Next
  Sheets("Sheet1").rows(4).copy Sheets("Sheet2").range("A4")
  Sheets("Sheet2").Range("A5").Resize(dic.Count, 1).Value = Application.Transpose(dic.keys)
  Sheets("Sheet2").Range("B5").Resize(dic.Count, 1).Value = Application.Transpose(dic.items)
End Sub

 

[Ismailfares]

thanks u helped me alot

for ur previous code ,it works correctly but the last row in unique values (first column ) write 1899 beside the whole sum i do not know why

i want to write sum not 1899 at the last row of this table

iam using it dic(Year(a(i, 1))) = dic(Year(a(i, 1))) + a(i, 10)

and i have some rows without years ,which code should i add to give the same function unavailable in frst column and in the second column result of sum empty years

i want the result to be in good shape as picture.Thanks

 

[DanteAmor]

From the macro before the new one, the only thing that changes is the resulting rows.
To understand what you need you could give examples of what you have and the expected result.

You can paste cell ranges using the following tool:

Upload an excel range:
XL2BB - Excel Range to BBCode