VBA to Compile Data from One Sheet to Other Sheets

[nabsher1]

Dear All,

I hoe you will be doing well.

Please be kind to extend your Benevolence to assist me to achieve the task using excel VBA to sum data base on Product ID and display
output in other sheet of the workbook.


Product ID Product Name Sale Comments Stock Pay
1 GEAR
2 GEAR
3 GEAR
4 GEAR
5 GEAR


I have attached illustration and sample workbook for your kind reference.


Note: Attachment is Neat and Clean, Feel free to open....


Your swift reply would highly be appreciated.

Best Regards,

[JohnTopley]

Formula solution

in "GEAR" C8

Please Login or Register  to view this content.

Column A of "Data" is Date/Time so convert to DATE only

[JohnTopley]

Please Login or Register  to view this content.

[nabsher1]

Thank you @JohnTopley

It perfectly serves for the required purpose and works like a magic...

Thank you so much for your time and prompt support...Big time Appreciated!!!!!

[nabsher1]

Dear Bro @JohnTopley,

I am sorry to get back to you again.

Actually, this project need to be run in office 2003. Your solution/Code works fine with Office 2010 and higher.

But it gives error of SUMIFS in office 2003 as shown in the yellow highlighted area in attached screenshot/ code blow in red. coz it is not supported in office 2003

For l = 1 To 3 ' Loop through Sale, Stock, Pay
.Cells(srow + k - 1, ocol(l - 1)) = Application.SumIfs(dRng(l), _
ws1.Range("B:B"), sh(i), _
ws1.Range("C:C"), .Cells(srow + k - 1, "A"), _
ws1.Range("A:A"), ">=" & sDate, _
ws1.Range("A:A"), "<=" & WorksheetFunction.EoMonth(sDate, 0))
Next l

Please be kind to modify SUMIFS and replace it with something else to be worked in older versions using VBA.

Attachment 832945

Thank you!

[JeteMc]

Note that selecting Attachment 832945 results in the following: "Invalid Attachment specified. If you followed a valid link, please notify the administrator".
That said if you want a version 2003 compatible substitute for the SUMIFS formula in post #2, try:

Please Login or Register  to view this content.

Let us know if you have any questions.

[nabsher1]

Dear @JeteMc

My request in the post is about VBA code not excel formula.... Could you be help to modify the following code to make it work in Excel VBA 2003.

For l = 1 To 3 ' Loop through Sale, Stock, Pay
.Cells(srow + k - 1, ocol(l - 1)) = Application.SumIfs(dRng(l), _
ws1.Range("B:B"), sh(i), _
ws1.Range("C:C"), .Cells(srow + k - 1, "A"), _
ws1.Range("A:A"), ">=" & sDate, _
ws1.Range("A:A"), "<=" & WorksheetFunction.EoMonth(sDate, 0))
Next l


Regards,

[jindon]

nabsher1,

No need to prepare each Product sheet,
Try the attached.

Please Login or Register  to view this content.

[JohnTopley]

Please Login or Register  to view this content.

[JohnTopley]

A work-around BUT you really must upgrade Excel and get rid of 2003

Please Login or Register  to view this content.

[nabsher1]

Dear Bro John,

Thank you so much for your kindness and helping me out.

Regards,
Nabsher