I've got this really long macro that does exactly what I want, but a large chunk of the code at the end seems repetitive to me. But it was the only way I could figure to do what I need. I'm just wondering if some of it could be consolidated.
I'll attach my practice workbook with a sheet before the macro runs at all, a second sheet where I've only run the macro to just before the repetitive part and a third sheet showing after the whole macro runs.
Basically, what I have it doing is adding a total at the end of each section in "Account Activity Details", then adding a formula in H & I of the "Grand Total" row to sum up all of the section totals. Then, just below that, it adds a formula in I to sum up those totals. I had to designate the start of each section, then refer to the cells before or after them to tell it where to put the section totals. I can't refer to specific row numbers, because, naturally, the reports are different lengths each month.
If this is already a good way to do it, then I'm fine with that, but if there's something more efficient, that'd be good too.
Below is that part of the code:
Set FCS = Range("B:B").Find("*First Cost*")
Set INS = Range("B:B").Find("*Invoices*")
Set FBS = Range("B:B").Find("*Freight*")
Set LS = Range("B:B").Find("*Load")
Set ARS = Range("B:B").Find("*Account Receipts")
Set CS = Range("B:B").Find("*Claims")
Set ERS = Range("B:B").Find("*Exchange*")
Set LCS = Range("B:B").Find("*True Landed*")
LR = Range("B" & Rows.Count).End(xlUp).Row
Set SL = Range("F:F").Find("Grand Total")
With Range("H" & INS.Row - 2)
.Formula = "=SUBTOTAL(9,H" & FCS.Row + 4 & ":H" & INS.Row - 3 & ")"
End With
With Range("I" & INS.Row - 2)
.Formula = "=SUBTOTAL(9,I" & FCS.Row + 4 & ":I" & INS.Row - 3 & ")"
End With
With Range("H" & FBS.Row - 2)
.Formula = "=SUBTOTAL(9,H" & INS.Row + 4 & ":H" & FBS.Row - 3 & ")"
End With
With Range("I" & FBS.Row - 2)
.Formula = "=SUBTOTAL(9,I" & INS.Row + 4 & ":I" & FBS.Row - 3 & ")"
End With
With Range("H" & LS.Row - 2)
.Formula = "=SUBTOTAL(9,H" & FBS.Row + 4 & ":H" & LS.Row - 3 & ")"
End With
With Range("I" & LS.Row - 2)
.Formula = "=SUBTOTAL(9,I" & FBS.Row + 4 & ":I" & LS.Row - 3 & ")"
End With
With Range("J" & LS.Row - 2)
.Formula = "=SUBTOTAL(9,J" & FBS.Row + 4 & ":J" & LS.Row - 3 & ")"
End With
With Range("K" & LS.Row - 2)
.Formula = "=SUBTOTAL(9,K" & FBS.Row + 4 & ":K" & LS.Row - 3 & ")"
End With
With Range("H" & ARS.Row - 2)
.Formula = "=SUBTOTAL(9,H" & LS.Row + 4 & ":H" & ARS.Row - 3 & ")"
End With
With Range("I" & ARS.Row - 2)
.Formula = "=SUBTOTAL(9,I" & LS.Row + 4 & ":I" & ARS.Row - 3 & ")"
End With
With Range("H" & CS.Row - 2)
.Formula = "=SUBTOTAL(9,H" & ARS.Row + 4 & ":H" & CS.Row - 3 & ")"
End With
With Range("I" & CS.Row - 2)
.Formula = "=SUBTOTAL(9,I" & ARS.Row + 4 & ":I" & CS.Row - 3 & ")"
End With
With Range("H" & ERS.Row - 2)
.Formula = "=SUBTOTAL(9,H" & CS.Row + 4 & ":H" & ERS.Row - 3 & ")"
End With
With Range("I" & ERS.Row - 2)
.Formula = "=SUBTOTAL(9,I" & CS.Row + 4 & ":I" & ERS.Row - 3 & ")"
End With
With Range("H" & LCS.Row - 2)
.Formula = "=SUBTOTAL(9,H" & ERS.Row + 4 & ":H" & LCS.Row - 3 & ")"
End With
With Range("I" & LCS.Row - 2)
.Formula = "=SUBTOTAL(9,I" & ERS.Row + 4 & ":I" & LCS.Row - 3 & ")"
End With
With Range("H" & LR + 2)
.Formula = "=SUBTOTAL(9,H" & LCS.Row + 4 & ":H" & LR & ")"
End With
With Range("I" & LR + 2)
.Formula = "=SUBTOTAL(9,I" & LCS.Row + 4 & ":I" & LR & ")"
End With
With Range("H" & SL.Row)
.Formula = "=SUBTOTAL(9,H" & FCS.Row & ":H" & LR & ") +" & "J" & LS.Row - 2
End With
With Range("I" & SL.Row)
.Formula = "=SUBTOTAL(9,I" & FCS.Row & ":I" & LR & ") +" & "K" & LS.Row - 2
End With
With Range("I" & SL.Row + 1)
.FormulaR1C1 = "=Sum(R[-1]C[-1]:R[-1]C)"
End With
Thanks!
Jenny
Bookmarks