Good evening. I have a Productivity Report that contains very basic formulas that provide totals for 4 columns (B6:E6) and an average for one column (F). I have included two command buttons, one to add a new row and the other to delete a row.
I need to be able to add or delete rows depending on how many employees' productivity I will be tracking on any given week; each row represents a separate employee. I need the following functionality out of my form:
1) formula in column F needs to copy and paste with each new line
2) when a new line is copied and pasted I need the contents to be cleared
3) I need the user to be blocked from deleting the first row (3 on this form) in the table
The code I'm using for my "Add" button is:
The code I'm using for my "Delete" button is:Sub AddEE() ' ' AddEE Macro ' ' Range("A3:H3").Select Selection.Copy Range("A4:H4").Select Selection.Insert Shift:=xlDown Selection.ClearContents Application.CutCopyMode = False Range("B5").Select ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)" Range("C5").Select ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)" Range("D5").Select ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)" Range("E5").Select ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)" Range("F5").Select ActiveCell.FormulaR1C1 = "=AVERAGE(R[-2]C:R[-1]C)" Range("G5").Select End Sub
The buttons add and delete rows as I'd like them to but content is not clearing, with each row added the contents provide a sub total. I've tried various lines of code (some more complicated and some less) before I recorded my own macro (see above). Any thoughts or suggestions would be appreciated.Sub DeleteRow() ' ' DeleteRow Macro ' ' Range("A3:H3").Select Selection.Delete Shift:=xlUp Range("C15").Select End Sub
Matthew
Bookmarks