Need help to adjust Sumifs formula in vba code on another worksheet

[RAM72]

Hi All

I have a vba code for sumifs for data and summary sheet in yellow tabs, Macro found on summary sheet (yellow)

Need to adjust macro for red tabs data_1 and summary report_1. Columns involved in data_1 are columns H,I,J,K,Min blue.

An advanced filter for unique records is made through macro with their respective headers in summary report _1

Can anyone help to adjust code for summary report_1 columns involved are D and E in red as attached

Sub RAM()
Dim LastR As Long
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("data")
Set ws2 = Sheets("summary")


LastR = ws1.Cells(Rows.Count, 1).End(xlUp).Row
    With ws2
        .Range("A1:E1").Value = ws1.Range("A1:E1").Value
        .Range(.Range("A2"), .Cells(LastR, 4)).Value = ws1.Range(ws1.Range("A2"), ws1.Cells(LastR, 4)).Value
        .Range(.Range("A2"), .Cells(LastR, 4)).RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlNo
       
        LastR = .Cells(Rows.Count, 1).End(xlUp).Row
        .Range(.Range("E2"), .Cells(LastR, 5)).Value = _
        "=SUMIFS(data!C,data!C[-4],RC[-4],data!C[-3],RC[-3],data!C[-2],RC[-2],data!C[-1],RC[-1])"
        .Range(.Range("E2"), .Cells(LastR, 5)).Value = .Range(.Range("E2"), .Cells(LastR, 5)).Value
    End With
End Sub

 

[Nebu]

Hi:

I have created named ranges for the macro, right click on the Summary Report_1 tab select view code and paste the following code there.

Sub test()
Application.ScreenUpdating = False
i& = Me.Cells(Rows.Count, "A").End(xlUp).Row

Me.Range("D2").Formula = "=SUMIFS(Qty,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("D2:D" & i).FillDown
Application.ScreenUpdating = True
End Sub

Thanks

 

[RAM72]

Hi:

I have created named ranges for the macro, right click on the Summary Report_1 tab select view code and paste the following code there.

Sub test()
Application.ScreenUpdating = False
i& = Me.Cells(Rows.Count, "A").End(xlUp).Row

Me.Range("D2").Formula = "=SUMIFS(Qty,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("D2:D" & i).FillDown
Application.ScreenUpdating = True
End Sub

Thanks

Hi Nebu

Thanks works well but does sumifs only for column D only.

Could you adjust code so that macro works simultaneously for column D and E

 

[Nebu]

Hi:

We expect you to put little bit of effort from your end , after all this is your report. You are getting solution here for free, it does not mean that we should do all the work for you. Replicating the same logic for column E was not a big task. If you are not ready to put in a bit of effort from your end to understand the codes/formulas do not expect solutions from our end. I can guarantee you that all the ninjas here are not born with excel skills, we had put our bit of effort to learn the bit of excel we know. Anyhow , here is the code including column E.

Sub test()
Application.ScreenUpdating = False
i& = Me.Cells(Rows.Count, "A").End(xlUp).Row

Me.Range("D2").Formula = "=SUMIFS(Qty,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("D2:D" & i).FillDown
Me.Range("E2").Formula = "=SUMIFS(montant,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("E2:E" & i).FillDown
Application.ScreenUpdating = True
End Sub

Thanks

 

[RAM72]

Hi:

We expect you to put little bit of effort from your end , after all this is your report. You are getting solution here for free, it does not mean that we should do all the work for you. Replicating the same logic for column E was not a big task. If you are not ready to put in a bit of effort from your end to understand the codes/formulas do not expect solutions from our end. I can guarantee you that all the ninjas here are not born with excel skills, we had put our bit of effort to learn the bit of excel we know. Anyhow , here is the code including column E.

Sub test()
Application.ScreenUpdating = False
i& = Me.Cells(Rows.Count, "A").End(xlUp).Row

Me.Range("D2").Formula = "=SUMIFS(Qty,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("D2:D" & i).FillDown
Me.Range("E2").Formula = "=SUMIFS(montant,Tariff,A2,Description,B2,Origin,C2)"
Me.Range("E2:E" & i).FillDown
Application.ScreenUpdating = True
End Sub

Thanks

Hi

Do agree, thanks for your kind help

 

[murugaa]

Dear Sir
1 Date Wise Report
2 Type Casse wise
3 Date wise cash Abstract
Kindly need sir your help
Thking you Sir.

 

[vletm]

murugaa
You should have opened a new thread
as You've read from Forum Rules.
This thread is closed now.