Better VBA Code to Insert Blank Row and Copy a Cell

[tsroque]

Hey Guys -

This VBA code works, but just wondering if there is a better way to write it.

PURPOSE/GOAL: User can add additional time to a day on a timesheet. Example: Employee works two shifts. Timesheet has In/Out/In/Out columns (4) so when they clock in more than 2x, we have to insert an additional row. The users are not Excel experts so I need to make inserting a row as user-friendly as possible.

Sub AddRow()
'Inserts and Copies a Row Below a Selected Cell with Same Formatting
    With ActiveCell
        .EntireRow.Copy
        .EntireRow.Insert
    End With
    Application.CutCopyMode = False
    
' Clear Cells in Range D:G
    ActiveCell.Offset(1, 2).Select
        ActiveCell = Null
    ActiveCell.Offset(0, 1).Select
        ActiveCell = Null
    ActiveCell.Offset(0, 1).Select
        ActiveCell = Null
    ActiveCell.Offset(0, 1).Select
        ActiveCell = Null
        
' Clear Cells in Range P:Q
    ActiveCell.Offset(0, 9).Select
        ActiveCell = Null
    ActiveCell.Offset(0, 1).Select
        ActiveCell = Null
        
' Clear Cell, Copy Above Cell and Paste VALUE to Cell Below
    ActiveCell.Offset(0, -15).Select
        ActiveCell = Null
    ActiveCell.Offset(-1, 0).Select
        ActiveCell.Copy
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    
End Sub

Thank you!

 

[My Aswer Is This]

Do you really want to clear all the values in column "D:G" and columns ("P:Q")

If so you can do it this way:

Range("D:G,P:Q").ClearContents

And maybe you can explain a little more what you want to do.

Your script is doing a lot more then just inserting a row.
And it's not a really a go ideal to use active cell. When the script runs you can mess up if your active cell is not where you want it.