Macro to insert row/column and copy the formula from the preceding row/column

[_pipo_]

Hi,

I have quite a complex macro question to ask (at least in my mind anyway ), but seeing as though people on here like a challenge i thought i'd post it:

First of all see screenshot of my spreadsheet for reference:

What i am trying to do is to create separate macros to do the following:

1. Add a new row after Row 6, and so on, so that it copies the formatting and the formulas of the previous row and that any values generated will be included in the subtotals row (currently in I8, J8 & K8, which will obviously move down as a row is entered each time.
2. Similarly, i need to add a column after Column K, which will 'copy+1' the preceding column [so in my example L2 becomes Apr and L3 becomes =(COUNTIF($B3:$G3,COLUMN(D1)-COLUMN($A1)+1))*$H3], and a subtotal in L8 as well.

There are more things i want to do to this but i thought i would share this elephant bit by bit. Props to anyone who can share their tuppence.

Cheers,
pipo

 

[SamTYler]

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/10/2010 by samT
'
'
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight
End Sub

 

[_pipo_]

Hi,

Thanks for the input.

I now have the following macro for adding a row below the last entry (Row 6)

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/10/2010 by pipo
'
'
    Rows("6:6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Rows("7:7").Select
    Selection.Insert Shift:=xlDown
End Sub

The trouble with this macro is that it's copying the entire row as opposed to only the ones with a formula (I - L) so i need one that inserts a new row below and only copying cells with a function in it.

What i would also like to do is to add more columns after G (to make 'Cust7', and so on) - at the same time being included in the COUNTIF formulas in the cells from Column I onwards.

Hope that makes sense.

 

[_pipo_]

Bumped for anyone who hasn't seen this. Is anyone able to help?