Hello!
I am needing some assistance! Here's the situation, At work everyday, someone in my team pulls the previous days ACH payments received and adds it to that days sheet in the cuirrent month's excel file. To save time and keep a consistent look from day to day, I built a macro to make the data more pleasing to the eye and easier to read. It worked great until the next day came about and we had more payments come in, meaning more rows of data so this is what the finished product comes out as:
How do I change the VBA so that the range of data isn't preset only to A17: G17?
Also, I'm not sure if this would be included or not but I need to put a total row at the end and total up the payments in column E.
This is what the VBA looks like for the macro I built, Any assistance is greatly appreciated!!! Thank youi!!
Sub MAKEemPRETTY()
'
' MAKEemPRETTY Macro
' Formats the list of daily EFT's to be visually appealing
' Keyboard Shortcut: Ctrl+m
'
Cells.Select
Cells.EntireColumn.AutoFit
Range("E17").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Columns("E:E").Select
Range("E3").Activate
Selection.Style = "Currency"
Columns("A:A").Select
Range("A3").Activate
Selection.NumberFormat = "mm/dd/yyyy"
Range("A17:D17,F17:G17").Select
Range("F17").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("A:B,F:G").Select
Range("F3").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("A17:G17").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 13434624
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A17:G17").Select
Selection.Font.Bold = True
Range("A17:D17").Select
ActiveCell.FormulaR1C1 = "TOTAL"
Range("A17:D17").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Range("A1:G17").Select
Range("F17").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A5:G5").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 16770559
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A4:G5").Select
Selection.Copy
Range("A6:G16").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("F19").Select
Columns("F:F").ColumnWidth = 43.29
Columns("F:F").Select
Range("F3").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("J9").Select
End Sub