VBA Player Piano: Coda
April 20, 2020, 11:33 p.m.
By now I felt comfortable working with VBA as an extension of day-to-day excel. The next month-end macro had more moving parts, but the overarching logic was the same. I could draw on my knowledge of previous scripts and adapt them. Luckily this macro would be for two reports which were similar, with the weekly one being a scaled down one of the month-end. I decided to knock out the month-end version first.
The biggest issue with this one was that there were 11 worksheets in one workbook. Luckily I was familiar with the process, and 5 of these worksheets were data dumps, while the other 4 required subsets of extracted information summaries from the main reporting sheet. The last sheet is a secondary reporting sheet for a different data dump.
Like in the previous post, declaring and setting the workbook, worksheets, and last rows, then resizing the reporting sheet based on the total rows of the dump, then copying the values in was standard. As was the VLOOKUP aspect for all the additional columns complete with auto fill and value pasting. You can review that post to see how some of the code worked.
The deviation started where I had to filter based on certain criteria pasted into the main report, by making a copy of that data into another sheet to perform new calculations. VBA kept copying the entire value range and not merely the visible filtered range.
Since I had the original detailed data dump, rather than keep grokking at VBA's copy value by special cell only (which I couldn't get to work as intended), I decided to filter on the original data dump and copy them to the next manipulation sheet into check columns in full:
With DetailDump.Range("A1:L1")
If DetailDump.AutoFilterMode Then DetailDump.AutoFilter.Range.AutoFilter
.AutoFilter Field:=3, Criteria1:="TYPE_TO_SUBTOTAL"
.AutoFilter Field:=2, Criteria1:="=*CATEGORY_1", _
Operator:=xlOr, Criteria2:="=*CATEGORY_2"
This would give me all the data points that needed subtotaling for a specific column in the main report. I found some lines of code on stackOverflow to end up with the filtered range only remaining in my new sheet. I'd use the row count and 2 arrays for the 2 columns to first copy from the detailed data dump to the subtotaled sheet, then loop through the range, and if the cell in the Detailed Dump was flagged as hidden (filtered), then it would delete that row in the subtotaled sheet. It was the best I could find to make sure I only ended up with the filtered types in my new sheet. I would have prefered to copy the visible values only directly.
Me after failing at copying filtered values only
Here's the stackOverflow modified code that gave me the results I wanted:
RowCountTemp = DetailDump.Range("A" & Rows.Count).End(xlUp).Row
arrA = DetailDump.Range("$A$2:$A$" & RowCountTemp).Value
arrB = DetailDump.Range("$E$2:$E$" & RowCountTemp).Value
MainSheet.Range("$A$2:$A$" & RowCountTemp).Resize(UBound(arrA, 1), UBound(arrA, 2)) = arrA
MainSheet.Range("$B$2:$B$" & RowCountTemp).Resize(UBound(arrB, 1), UBound(arrB, 2)) = arrB
Dim i, j As Long
For i = MainSheet.Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
If (DetailDump.Range("A" & i).EntireRow.Hidden) Then MainSheet.Range("A" & i).Delete
Next i
For j = MainSheet.Range("B" & Rows.Count).End(xlUp).Row To 1 Step -1
If (DetailDump.Range("B" & j).EntireRow.Hidden) Then MainSheet.Range("B" & j).Delete
Next j
DetailDump.ShowAllData
The next challenge was to take this filtered data dump and provide subtotals for the IDs to feed back into a new column in the main report. Again what seems intuitive in a typical excel button is a bit more involved with VBA. And this had to be done for two different subtotals based on 3 different criteria. Luckily once I could figure out the first subtotal solution, the next ones would just require modified parameters and locations.
First we want to find the last row in the subtotal range, save that in a variable, then call the Excel Subtotal Function. After we want to select Tab 2 to only show the subtotaled numbers. To avoid another nested for loop, and since it's in the same sheet with less data, I decided to simply explicitly copy/paste the special cells (rather than Range.Value) which would give me the subtotal numbers only. Since the Excel format for subtotals give us "Total 123", we'll simply replace "Total " with "" to give us only the number.
' #### Subtotaled Amount ####
MainSheetRangeSubTotal = DetailDump.Range("A" & Rows.Count).End(xlUp).Row
MainSheet.Range("A1:B" & BilledRange).Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2)
MainSheet.Outline.ShowLevels RowLevels:=2
Dim SubTotalCopy
Dim SubTotalPaste
Set SubTotalCopy = MainSheet.Range("A2:B" & MainSheetRangeSubTotal).SpecialCells(xlCellTypeVisible)
Set SubTotalPaste = MainSheet.Range("E2:F" & MainSheetRangeSubTotal)
SubTotalCopy.Copy
SubTotalPaste.PasteSpecial xlValues
' #### Replace ' Total' txt ####
MainSheet.Range("E2:E" & MainSheetRangeSubTotal).Replace What:=" Total", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next, it's a matter of performing a VLOOKUP of the subtotals to the user ID, autofilling down, replacing #N/As with 0 and copying all as value. The entire process is similar to the second category of subtotals. The only reason I didn't perform them together is because the main report requires 2 different subtotals for 2 different categories, thus they can't be merged.
The subtotals from the second category are also a bit more involved in that once the subtotals have a VLOOKUP performed to the main sheet, we have to again check the user IDs to see if any of the categories have a $0 value in the original report but a subtotal value from the recently created list. For all cases like that we have to dynamically add more rows on the bottom of our main report, then copy the User ID/Totals from the 2nd subtotal sheet and then VLOOKUP the remaining datapoints to basically bring in these $0/>$0 users into the main report.
We then have two further summary pages based on inactive/active users that will be dynamically resized to match the main report. At first we'll simply copy the main report to both of these summary pages. We'll then autofilter and delete all rows that either aren't active/inactive depending on the sheet. I found this to be the fastest, least error-prone way to accomplish this. If there are no inactives, the sheet will remain empty and move on to the next line.
I initially didn't have to worry about any exception or error handling as I'd engineered the macro to perform exactly what was necessary. Exceptions are quite exceptional as they come at you when you least expect it. At the end of the year there was a change in some classifications and a certain category would now show up as "inactive" at all. The previous code always took inactive into account, so if there were no inactive, the macro would fail. So a simple error handler was created that would check if any existed and skipped over if nothing. Later on, inactives were reluctantly added back in, so now the macro is more robust, being able to handle a mix, or all or nothing.
We can then copy the totals for both active/inactive categories based on their bucket lists into a master summary page. With that all of the transformations required for the main report and its subsidiaries is finished.
Now there's a secondary report based on loans. We first make sure we have the dump data relevant to this report then format it using our various VLOOKUPs from our listing. The reviewing managers don't want the user names, owners or IDs repeating in this report so to the right margin of the report I create a check formula that will count if the value is the same as prior, and if so to make it blank until the previous value is different than the first. I created 3 formulas on 3 different columns and then deleted the temporary formulas after the formatting was finished to clean the report up:
Details.Range("J6").Formula = "=IF(COUNTIF($A$6:A6,A6)=1,B6, """")"
Details.Range("K6").Formula = "=IF(COUNTIF($B$6:B6,B6)=1,C6, """")"
Details.Range("L6").Formula = "=IF(COUNTIF($B$6:B6,B6)=1,D6, """")"
Details.Range("J6:L6").AutoFill Destination:=Details.Range("J6:L" & lastRow)
Details.Range("J:L").Delete
Here's an example of the secondary summary table template without duplicates in the 2nd-4th columns:111 Name1 Contact1 Manager1 123456 1-Jan-70 100.00 Type 1
111 654321 1-Feb-70 200.00 Type 2
111 789456 1-Mar-70 300.00 Type 3
222 Name2 Contact2 Manager2 555555 31-Dec-88 100.00 Type 1
222 444444 31-Dec-91 100.00 Type 1
222 33333 31-Dec-93 100.00 Type 1
222 222222 4-Jul-76 100.00 Type 1
222 111111 4-Jul-76 100.00 Type 1
Next I took the last 3 columns of the secondary report and created a pivot table based off the data, tailored to the reviewing manager's preference. I didn't feel like learning every intimate detail of pivot table code creation, so in this case I used the newbie point-and-click method to record a macro of me creating a pivot table of the desired data.
I resumed my coding by copying the pivot data into a new sheet as value so I wouldn't have to worry about any formatting issues, then delete the pivot table temporary sheet. Luckily this summary pivot sheet based off the secondary report is already present as a template before the entire macro is run. It comes complete with an amortization table based on fiscal year with pre-filled formulas on the first row that get auto-filled at the end once the pivot table data is pasted.
Total by Fiscal Year (goes on several years):
=SUMIFS($I$7:$I$93,$A$7:$A$93,"<=4/30/2020",$A$7:$A$93,">=5/1/2019")
Then there are two columns called at the end of the secondary summary report, including one for the total by year, and the second for the fiscal year billed. An HLOOKUP is used to make sure that the total is only placed on the last line of that fiscal year. The first HLOOKUP is pre-filled on the table. Once the resizing and autofilling happens towards the end, the rest of the row will populate with the formula. The same for figuring out where the fiscal year total is.
HLOOKUP Formula:
=IFERROR(HLOOKUP(K20,$M$5:$S$6,2,),"")
And like in the detailed secondary report page before, I included an IF COUNTIF formula pre-filled to the right of these 2 columns to check whether the Fiscal Year repeats, and if it does only take the last instance in the sequence:
=IF(COUNTIF($K7:$K$91,K7)=1,K7,"")
The script will then copy all the values in the range from that check column and paste it into the actual Fiscal Year column. This will ensure the fiscal year date ends at precisely where the last date entry in that fiscal year is.
Finally we want to total the amount by fiscal year. The second column formula comes pre-filled as follows, and will populate after the IF/COUNTIF + its value copy takes place.
Ridiculous looking IF/AND Statement to check for Fiscal Year for the next few years:
=IF(AND(A7>="5/1/2019"+0, A7<="4/30/2020"+0), "2019", IF(AND(A7>="5/1/2020"+0, A7<="4/30/2021"+0), "2020", IF(AND(A7>="5/1/2021"+0, A7<="4/30/2022"+0), "2021", IF(AND(A7>="5/1/2022"+0, A7<="4/30/2023"+0), "2022", IF(AND(A7>="5/1/2023"+0, A7<="4/30/2024"+0), "2023", IF(AND(A7>="5/1/2024"+0, A7<="4/30/2025"+0), "2024", IF(AND(A7>="5/1/2025"+0, A7<="4/30/2026"+0), "2025","RUN MACRO")))))))
The above bloated IF/AND statement checks all the dates in column A for their precise location related to fiscal year. Column K will only populate with the fiscal year if the date is in the right range, which can be is checked by the adjacent column having a value or not.
If the script executes successfully (at this point for our team's purposes, the only way it wouldn't is if one of the data dumps was pasted incorrectly), one will see a nice message saying the report has been fully formatted.
The weekly version is similar to the above mentioned monthly version, the exception being there's only one column of subtotals and no secondary reports, thus having less overall worksheets. Also only the main sheet is needed, no summaries separate analyses anywhere so at the end of the script I delete all extraneous sheets to give the report a lean, clean one sheet presentation for whoever's reviewing the weekly version.
A few months later we were in a situation where there were no inactives, as well as one category not having loans. I ran the macro and found 2 nasty crashes. A simple wrapping in error handlers and value checking brought the macro back to its smooth form, now taking into account more situations.
**Update 4/20/20**
Due to the economic fallout from 2020's crisis, many of our business partners were in dire straits. Upper management required our weekly report to now include the loans. I created a third report that took aspects from the weekly and the monthly to create a further streamlined report that would give the overall summary in 1 tab, then the detailed loan info on the 2nd and the aestheticly formatted summary on the 3rd tab.
As the new fiscal year was approaching, I also realized that my FY formulas needed to be more robust (rather than hardcoded from the year before).
I modified the loans summary page to take a formula that dynamically displays the current fiscal year (May 1 being Day 1) then calculate the next 6 years in the cycle based off that. The SUMIFs were all then modified to only add based on their relative position to current fiscal year.
' Example of FY + 3My colleague's only regret was that now "she felt useless and bored" because she couldn't spend a few hours manually formatting this spreadsheet. #automatingproblems. If she wants to spend every 1st and 2nd of the month as well as every Friday putting in extra time, I said she's more than welcome to ignore my macro. To this day, I haven't seen her stay past her scheduled time.
=SUMIFS($I$7:$I$96,$A$7:$A$96,"<="&(Q2+365*3),$A$7:$A$96,">="&(P2+365*3))
