I have a followup question to an earlier post.
The formulas are all working great. However, I need for the coding to overlook 3 customers when selecting the "Top 5 Customers".
These customers are GMIT, BGS GRM, and GMIT SC.
I'm not sure how to incorporate this into what I have currently. Also, can this be shortened at all?
This is the original post:
https://www.mrexcel.com/forum/excel...ch-top-5-outstanding-amounts-offset-name.html
Here is the code I have.
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The formulas are all working great. However, I need for the coding to overlook 3 customers when selecting the "Top 5 Customers".
These customers are GMIT, BGS GRM, and GMIT SC.
I'm not sure how to incorporate this into what I have currently. Also, can this be shortened at all?
This is the original post:
https://www.mrexcel.com/forum/excel...ch-top-5-outstanding-amounts-offset-name.html
Here is the code I have.
Code:
[TABLE="width: 128"]
<tbody>[TR]
[TD="colspan: 2"]Sub OverdueReporting()
[/TD]
[/TR]
[TR]
[TD]'[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]' Overdue Aging Macro[/TD]
[/TR]
[TR]
[TD]'[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]'[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("H1").Select
[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=MID(RC[-1],13,10)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("H1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.NumberFormat = "mm/dd/yy;@"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("I1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.SpecialCells(xlCellTypeLastCell).Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "X"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("M4").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=IF(LEFT(RC8,3)=""net"",MID(RC8,5,2),0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Columns("O:O").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.NumberFormat = "0.00"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("O5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = _[/TD]
[/TR]
[TR]
[TD="colspan: 2"] "=IFERROR(IF(OR(R1C[-7]-RC[-10]>1000,R1C[-7]-RC[-10]<0),0,R1C[-7]-RC[-10]),0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("P5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=IF(and(RC15>0,RC15<8),RC11,0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Q5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=IF(AND(RC15>7,rc15<15),RC11,0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("R5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=IF(AND(RC15>15,RC15<31),RC11,0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("S5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=IF(AND(RC15>30,RC15<60),RC11,0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("T5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=IF(RC15>59,RC11,0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("V5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=IF(RC[-21]=""Customer Totals:"",RC[-20],0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("M4").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("M5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range(Selection, Selection.End(xlDown)).Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("M5:y5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("M6:y6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range(Selection, Selection.End(xlDown)).Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("P3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "'1-7 dys"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Q3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "'8-14 dys"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("R3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "'15-30 dys"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("S3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "'31-60 dys"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("T3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "'>60 dys"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("y1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "LARGE"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Z1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "VALUE"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("AA1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "ROW"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("AB1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "CUSTOMER"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Y2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "'1"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Y3").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "'2"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Y4").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "'3"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Y5").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "'4"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Y6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "'5"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Z2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=LARGE(R4C[-4]:R1000C[-4],RC[-1])"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("AA2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=MATCH(RC[-1],C[-5],0)"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("AB2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = _[/TD]
[/TR]
[TR]
[TD="colspan: 2"] "=LOOKUP(2,1/((R4C[-27]:INDEX(C[-27],RC[-1])<>""INV"")*(R4C[-27]:INDEX(C[-27],RC[-1])<>""CUSTOMER TOTALS:"")),R4C[-27]:INDEX(C[-27],RC[-1]))"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Z2:AB2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Z3:Z6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Z2:Z6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Style = "Comma"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Columns("m:U").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Style = "Comma"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("AE2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=+RC[-5]/1000"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("AE2").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.NumberFormat = "0.0000"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.NumberFormat = "0.000"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.NumberFormat = "0.00"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("AE3:AE6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("AB2:AE6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] With Selection.Interior[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Pattern = xlSolid[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .PatternColorIndex = xlAutomatic[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Color = 65535[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .PatternTintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Columns("AB:AB").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.ColumnWidth = 31[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Columns("AC:AC").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.ColumnWidth = 2.71[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Columns("AD:AE").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.ColumnWidth = 13.86[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("AD2:AE6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Borders(xlDiagonalDown).LineStyle = xlNone[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Borders(xlDiagonalUp).LineStyle = xlNone[/TD]
[/TR]
[TR]
[TD="colspan: 2"] With Selection.Borders(xlEdgeLeft)[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"] End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"] With Selection.Borders(xlEdgeTop)[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"] End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"] With Selection.Borders(xlEdgeBottom)[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"] End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"] With Selection.Borders(xlEdgeRight)[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"] End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"] With Selection.Borders(xlInsideVertical)[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"] End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"] With Selection.Borders(xlInsideHorizontal)[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .LineStyle = xlContinuous[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .ColorIndex = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Weight = xlThin[/TD]
[/TR]
[TR]
[TD="colspan: 2"] End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _[/TD]
[/TR]
[TR]
[TD="colspan: 2"] xlNone, SkipBlanks:=False, Transpose:=False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Cells.Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ThisWorkbook.Worksheets("Sheet1").Cells.EntireColumn.AutoFit[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("P1:T1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] With Selection.Interior[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Pattern = xlSolid[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .PatternColorIndex = xlAutomatic[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Color = 65535[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .PatternTintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"] With Selection.Font[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Name = "Calibri"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Size = 12[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Strikethrough = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Superscript = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Subscript = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .OutlineFont = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Shadow = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Underline = xlUnderlineStyleNone[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .Color = -16777216[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .TintAndShade = 0[/TD]
[/TR]
[TR]
[TD="colspan: 2"] .ThemeFont = xlThemeFontMinor[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("P1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=SUM(R[4]C:R[999]C)/1000"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("P1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Copy[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("Q1:T1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("U1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveCell.FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Columns("A:U").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Columns("A:U").EntireColumn.AutoFit[/TD]
[/TR]
[TR]
[TD="colspan: 2"] End With[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("p1:U1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Copy[/TD]
[/TR]
[TR]
[TD] ChDir _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] "H:\Accounting\ABC Services Inc\Accounting\Month End Financial Close\2019 Month End Close"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Workbooks.Open Filename:= _[/TD]
[/TR]
[TR]
[TD="colspan: 2"] "H:\Accounting\ABC SERVICES INC\Accounting\Month End Financial Close\2019 Month End Close\Overdue reporting template.xlsx"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("d8").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _[/TD]
[/TR]
[TR]
[TD="colspan: 2"] xlNone, SkipBlanks:=False, Transpose:=True[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveWorkbook.Save[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveWindow.Close[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("AB2:AE6").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Selection.Copy[/TD]
[/TR]
[TR]
[TD] ChDir _[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"] "H:\Accounting\ABC SERVICES Inc\Accounting\Month End Financial Close\2019 Month End Close"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Workbooks.Open Filename:= _[/TD]
[/TR]
[TR]
[TD="colspan: 2"] "H:\Accounting\ABC SERVICES INC\Accounting\Month End Financial Close\2019 Month End Close\Overdue reporting template.xlsx"[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("a16").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveSheet.Paste[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Application.CutCopyMode = False[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveWorkbook.Save[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveWindow.Close[/TD]
[/TR]
[TR]
[TD="colspan: 2"] Range("a1").Select[/TD]
[/TR]
[TR]
[TD="colspan: 2"] ActiveWorkbook.Save[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]End Sub
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Last edited by a moderator: