VBA Code:
Option Explicit
Sub GatherFiguresXXX()
Dim wksSource As Worksheet, i As Long, lastRow As Long, rng As Range
ActiveWorkbook.Sheets("Sheet1").Copy After:=ActiveWorkbook.Sheets(1)
Set wksSource = ActiveSheet: wksSource.Name = "Data"
' SORT DATA BY CUSTOMER, PRODUCT, THEN SUPPLIER
lastRow = wksSource.Range("A" & wksSource.Rows.Count).End(xlUp).Row
With wksSource.Sort
.Header = xlYes
.SortFields.Add Key:=Range("A1"), Order:=xlAscending
.SortFields.Add Key:=Range("B1"), Order:=xlAscending
.SortFields.Add Key:=Range("C1"), Order:=xlAscending
.SetRange Range("A1:BD" & lastRow)
.Apply
End With
'Make Subtotals:
wksSource.Range("A1:BD" & lastRow).Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4, 5), _
Replace:=True, Pagebreaks:=False, SummaryBelowData:=True
lastRow = wksSource.Range("B" & wksSource.Rows.Count).End(xlUp).Row 'last row on B:B for the sheet with subtotals
On Error Resume Next 'for the case of no subtotals...
Set rng = wksSource.Range("B2:B" & lastRow + 1).SpecialCells(xlCellTypeBlanks).Offset(, 1)
On Error GoTo 0
If Not rng Is Nothing Then
rng.ClearContents 'clear subtotals on column C:C
'emphasize Subtotals:
rng.Offset(, 1).Interior.Color = vbYellow
rng.Offset(, 2).Interior.Color = vbYellow
wksSource.Rows(lastRow + 2).Clear 'clear Grand Total row
End If
wksSource.Activate
End SubAlso, macro is in Module 2 in the below, Uncompleted Workbook
Uncompleted Workbook: Easyupload.io - Upload Files and Share Them Easily
Completed Workbook: Easyupload.io - Upload Files and Share Them Easily
Hello everyone!
I have:
A report which takes hourssss to complete and comes in weekly.
I do not have:
Any macro/VBA skills of any kind. I am admittedly a noob. I have been watching videos on VBA at work but they can only get you so far; especially because I cannot ask anyone on the video questions. Which is why I am here, hoping someone can help. Please do!
Preface:
My laptop for work is locked down tight. I cannot get on these sites and I definitely cannot send any sort of documents to a personal email address. With that said, I spent a couple of hours on my personal laptop and put together a spreadsheet to imitate where I am stuck at in my macro for work (I hope this is OK, I have no other choice). I cannot take credit for all of the coding as I did have some help from someone but their interest in helping me quickly dissipated unfortunately. If someone does help and the code works, I will adapt the code into my macro on my work computer.
The easiest way to explain what the report does (and how I put this spreadsheet together):
A Product for X Units and Y Price is Loaned and then later Returned for the same X Units and Y Price (the Units between Loan and Return(s) should net to 0). This is called a “Match.” However, the Returned Y price could be >, =, or < the Loaned Purchase Price. A Match is normally one Loan for X units and one Return for the same X units. The Total (Sum of Loan Price and Return Price) is needed for each Match. The company has millions of these which come through monthly and that specific figure is needed to search to the decimal with.
**Things to consider:**
(1.) There should be a Match for each Loan
(2.) The “Matches” are not always one Loan and one Return. Sometimes, there are two Returns, three Returns, etc. Units which amount to that Loan. **Example (in workbook):** for Customer 10009, Product ORANGES, and Supplies SPEARHEAD INC., there is a Loan of 125,000 Units, a Return of 45,000 Units, and another Return of 80,000 Units for a Total Return of 125,000 Units.
**PLEASE NOTE: Once or twice a year, the Unit value in Column E will not amount to 0. This is OK. The macro can move on to the next**
(3.) The Customer and Supplier will Loan and Return the same product multiple times. Although it is the same Customer and Supplier, this will create multiple Matches. **For example (in workbook)** Customer 10004 and Supplier SPEARHEAD INC. Loaned Product MATERIALS three times and Returned it three times. This will create 3 separate Matches and the macro would need to match the Returns with their corresponding Loans to “Match-up” before inserting the row to enter the formulas for each Match.
**What the Macro does:**
First, create a second sheet and rename it “data” to test macro. This way, I do not need to keep opening and closing the workbook to test the macro. Second, it sorts by: Customer (Column A), then by Product (Column B), and then Supplier(Column C). Third, it locates the Matches of Loan and Return by Product for each Customer and Supplier, inserts a row, and enters the Sum Formula in (i, “D”) & (i, “E”).
**What I need the Macro to do:**
As stated above, I am a noob and I have no idea where to start to do the following. ☹
(1.) The macro would need to use some sort of logic to recognize the Loan Unit amount for the Product (between the Customer and Supplier) and be able to reorganize the entire row for the Return Units of that Product to match it’s Loan. If at all possible, have the sheet formatted to always have the Loan on top and then its Return(s) underneath on the following rows.
(2.) If the Customer and Supplier Loans and Returns the same product multiple times, instead of treating it as one Match, be able to recognize the multiple Matches by number of Loans. **For example (in workbook)** Customer 10004 and Supplier SPEARHEAD INC. Loaned Product MATERIALS three times and Returned it three times. This will create 3 separate Matches and the macro would need to match the Returns with their corresponding Loans to “Match-up” before inserting the row to enter the formulas for each Match.
(3.) I do not know why but on rows 115-118, the macro bunched up two different Customers with two different Products (but had the same Supplier). If it could not do this and work correctly.
(4.) It not have that drop down list on the left hand side once it is completed. Not a huge deal, I think I could probably figure something out.
The Uncompleted Workbook is a sample of how the data comes in and the Completed Workbook is how I would hope the macro would turn the data into.
I would be extremely grateful for any help anyone is able to provide. Please let me know if you have any questions. Thank you ahead of time!
