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

hgeek23

New Member
Joined
Jul 16, 2010
Messages
14
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
 
Last edited:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

hgeek23

New Member
Joined
Jul 16, 2010
Messages
14
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:
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, 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:
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

New Member
Joined
Jul 16, 2010
Messages
14
This is what I got, but if there's another way of doing so, please let me know:

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, 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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,763
Messages
5,597,982
Members
414,198
Latest member
AMI_MINT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top