Inserting new columns and modifying existing VBA related code

**kriminaal** · 06-04-2015, 10:48 AM

First of I spent some time this morning trying to learn the basics of VBA and that's about the extent of my knowledge of it.
While I spend a great deal of time on excel work. I have never incorporated the use of VBA in it.

I found this auto-fill calendar online and would like to insert some rows in between on the To DO list.
Unlike excel the VBA code does not update so can someone tell me what I would need to change if I want to move the existing columns?
Calendar Template2.xlsm

**xladept** · 06-06-2015, 05:23 PM

Hi kriminaal,

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range, LastRow As Long, w As Worksheet
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set w = Sheets("Completed")
    
    ' The variable KeyCells contains the cells that will
    ' cause the macro to run when changed
    Set KeyCells = Range("D2:D" & LastRow)
    
    If Target.Count > 1 Then Exit Sub
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
  
    If Target.Value <> "" Then
        Target.EntireRow.Copy _
            w.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        Target.EntireRow.Delete Shift:=xlUp
    End If
  End If
End Sub