Auto-filling and other cells and inserting blank row when new data entered

[hgeek23]

Hi I have a really important issue in a data spreadsheet of mine.
I have seen various posts asking related questions but none that have really addressed what I need to accomplish.

I have a worksheet (attached) in which I have a set of data (historical returns of 2 things). The month is listed in column A, the returns and performance in the adjacent columns. The table is from A-E/

A couple of columns over (N-P), I have another table which calculates the performance of the two in months that there was a performance of greater than -3%.

the data of the 2nd table is included in a dynamic named range which includes the cells starting in row 3, proceeding down X rows until the first blank space.

I need to construct a method that will tell the spreadsheet to recognize that each time I add a new input to a new cell in column A (for the next month), the formulas in columns B-E and N-P auto-fill down another row.
Lastly, I need the worksheet to then add a blank row after the row just updated, as there needs to always be one row of separation between table 2, and a small table directly under it.

Can someone please help?

Link to spreadsheet:
http://hotfile.com/dl/55475777/a9f6dfd/sample_SP_DJI.xlsx.html

 

[hgeek23]

I've figured out how to do this with the sample spreadsheet. However, I am now truly stuck.

To do what I want for my previous issue with the sample provided I can do this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.Count = 1 And Target.Column = 1 Then
        If IsDate(Target.Value) Then
            Application.EnableEvents = False
            Target.Offset(1).Resize(1, 18).Insert xlDown
            Target.Offset(-1, 2).Copy Target.Offset(, 2)
            Target.Offset(-1, 4).Copy Target.Offset(, 4)
            Target.Offset(-1, 13).Resize(1, 3).Copy Target.Offset(, 13)
            Application.EnableEvents = True
        End If
    End If
    
End Sub</pre>

However, Say I wanted to make a Table with more columns and its starting parameters were not from A1-E1 and N1-P1 as previous, but to A6-AI6 and BG6-BV6?
Here's what i got as code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.Count = 1 And Target.Column = 1 Then
        If IsDate(Target.Value) Then
            Application.EnableEvents = False
            Target.Offset(1).Resize(1, 73).Insert xlDown
            Target.Offset(-1, 2).Copy Target.Offset(, 2)
            Target.Offset(-1, 34).Copy Target.Offset(, 34)
            Target.Offset(-1, 73).Resize(1, 16).Copy Target.Offset(, 73)
            Application.EnableEvents = True
        End If
    End If
    
End Sub</pre>

My problem is that It updates some of the cell values I want to update, but I want it to drag down the cells in columns E, G, I, K, (basically every other).
In table two, it drags down BG, BK, BO, and BS (corresponding to the months), but I also need it to drag down every cell, because they are basically all formulas with no hardcoded data (all cells in BG-BV)

Thanks!

 

[hgeek23]

This is what I got, but if there's another way of doing so, please let me know:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Cells.Count = 1 And Target.Column = 1 Then
        If IsDate(Target.Value) Then
            Application.EnableEvents = False
            Target.Offset(1).Resize(1, 74).Insert xlDown
            Target.Offset(-1, 2).Copy Target.Offset(, 2)
            Target.Offset(-1, 4).Copy Target.Offset(, 4)
            Target.Offset(-1, 6).Copy Target.Offset(, 6)
            Target.Offset(-1, 8).Copy Target.Offset(, 8)
            Target.Offset(-1, 10).Copy Target.Offset(, 10)
            Target.Offset(-1, 12).Copy Target.Offset(, 12)
            Target.Offset(-1, 14).Copy Target.Offset(, 14)
            Target.Offset(-1, 16).Copy Target.Offset(, 16)
            Target.Offset(-1, 18).Copy Target.Offset(, 18)
            Target.Offset(-1, 20).Copy Target.Offset(, 20)
            Target.Offset(-1, 22).Copy Target.Offset(, 22)
            Target.Offset(-1, 24).Copy Target.Offset(, 24)
            Target.Offset(-1, 26).Copy Target.Offset(, 26)
            Target.Offset(-1, 28).Copy Target.Offset(, 28)
            Target.Offset(-1, 30).Copy Target.Offset(, 30)
            Target.Offset(-1, 31).Copy Target.Offset(, 31)
            Target.Offset(-1, 32).Copy Target.Offset(, 32)
            Target.Offset(-1, 33).Copy Target.Offset(, 33)
            Target.Offset(-1, 34).Copy Target.Offset(, 34)
            
            Target.Offset(-1, 74).Resize(1, 17).Copy Target.Offset(, 74)
            Target.Offset(-1, 59).Copy Target.Offset(, 59)
            Target.Offset(-1, 60).Copy Target.Offset(, 60)
            Target.Offset(-1, 61).Copy Target.Offset(, 61)
            Target.Offset(-1, 63).Copy Target.Offset(, 63)
            Target.Offset(-1, 64).Copy Target.Offset(, 64)
            Target.Offset(-1, 65).Copy Target.Offset(, 65)
            Target.Offset(-1, 67).Copy Target.Offset(, 67)
            Target.Offset(-1, 68).Copy Target.Offset(, 68)
            Target.Offset(-1, 69).Copy Target.Offset(, 69)
            Target.Offset(-1, 71).Copy Target.Offset(, 71)
            Target.Offset(-1, 72).Copy Target.Offset(, 72)
            Target.Offset(-1, 73).Copy Target.Offset(, 73)
            Application.EnableEvents = True
        End If
    End If
    
End Sub