I have created a sreadsheet with a macro to total the sales, tax and tips, for both am and pm shifts. However, when I created the macro i recorded it to total in aspecific cell, instead of after the last cell that contained data. So if I have more sales during a shift than the one when I recorded the macro, it will only total up to the original cell.
I tried editing the code to add "& LastRow + 3" but that just gave me nothing. Here is my current macro, can any of you help me?
Code
Sub ThePressCafeShiftTips()
'
' ThePressCafeShiftTips Macro
' Calculates Tips per Shift
'
' Keyboard Shortcut: Ctrl+e
'
Columns("C:C").Select
Selection.EntireColumn.Hidden = True
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
Columns("E:L").Select
Selection.EntireColumn.Hidden = True
Columns("P:P").Select
Selection.EntireColumn.Hidden = True
Columns("R:R").Select
Selection.EntireColumn.Hidden = True
Columns("T:T").Select
Selection.EntireColumn.Hidden = True
Columns("U:W").Select
Selection.EntireColumn.Hidden = True
Columns("AB:AD").Select
Selection.EntireColumn.Hidden = True
Columns("AA:AA").Select
Selection.EntireColumn.Hidden = True
Columns("Z:Z").Select
Selection.EntireColumn.Hidden = True
Columns("A:A").ColumnWidth = 19.71
Columns("A:A").ColumnWidth = 28.86
Columns("B:B").ColumnWidth = 19.57
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
Columns("N:N").ColumnWidth = 12.71
Columns("O:O").ColumnWidth = 13.86
Columns("Q:Q").ColumnWidth = 23
Columns("Q:Q").Select
Selection.EntireColumn.Hidden = True
Columns("S:S").ColumnWidth = 18.71
Columns("S:S").ColumnWidth = 28.14
Columns("X:X").ColumnWidth = 22.86
Columns("X:X").Select
Selection.EntireColumn.Hidden = True
Columns("Y:Y").Select
Selection.EntireColumn.Hidden = True
Rows("1:1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("O:O").Select
Selection.NumberFormat = "$#,##0.00"
Columns("N:N").Select
Selection.NumberFormat = "$#,##0.00"
Columns("M:M").Select
Selection.NumberFormat = "$#,##0.00"
Rows("1:1").Select
Selection.AutoFilter
ActiveWindow.SmallScroll Down:=45
Range("O64").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-62]C:R[-4]C)"
Range("S64").Select
ActiveWindow.SmallScroll Down:=3
Range("N64").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-62]C:R[-4]C)"
Range("O64").Select
ActiveWindow.SmallScroll Down:=3
Range("M64").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=SUM(R[-62]C:R[-4]C)"
Range("N64").Select
ActiveWindow.SmallScroll Down:=3
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\H54047\Desktop\Erica's Test Tip Automation.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWindow.SmallScroll Down:=-18
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "AM Shift"
Sheets.Add After:=ActiveSheet
Sheets("Sheet2").Select
Windows("Payments-20210421_1213_CDT.csv").Activate
Selection.Copy
Windows("Erica's Test Tip Automation.xlsm").Activate
Cells.Select
ActiveSheet.Paste
Application.Run "'Erica''s Test Tip Automation.xlsm'!ThePressCafeShiftTips"
Windows("Erica's Test Tip Automation.xlsm").Activate
Application.Run "'Erica''s Test Tip Automation.xlsm'!ThePressCafeShiftTips"
Range("H17").Select
ActiveWindow.Close
Range("H15").Select
Application.Run "'Erica''s Test Tip Automation.xlsm'!ThePressCafeShiftTips"
Cells.Select
Application.Run "'Erica''s Test Tip Automation.xlsm'!ThePressCafeShiftTips"
Application.Run "'Erica''s Test Tip Automation.xlsm'!ThePressCafeShiftTips"
Application.Goto Reference:="ThePressCafeShiftTips"
Sheets("AM Shift").Select
Sheets("Sheet2").Select
Selection.Cut
Sheets.Add After:=ActiveSheet
Cells.Select
ActiveSheet.Paste
Application.Run "'Erica''s Test Tip Automation.xlsm'!ThePressCafeShiftTips"
Application.Goto Reference:="ThePressCafeShiftTips"
Cells.Select
Application.Run "'Erica''s Test Tip Automation.xlsm'!ThePressCafeShiftTips"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\H54047\Desktop\Erica's Test Tip Automation.xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWindow.SmallScroll Down:=-27
End Sub
Display More