macros to insert rows

Question

Hi

Looking for a macro to insert new rows and copy the formula from above on two identical sheets.

I have a row (Ref 715) adding all the data from the above rows. so I want the new row insertion just above

Row 715 on both the sheets.

Thanks for any support.

Kris

Answer 1

Should I add both the codes together. I'm new to VBA.

I copied the first code, it worked and subsequently I changed the revised

code provided above, and it says cannot be executed.

Additionally instead of telling the macro to add at Row 715, Can we change it to look

at the last row under Column A which has got a text "Total" and add row above this Row.

Sorry to bother you but your help is much appreciated

thanks

Answer 2

This does it for Sheet1 & Sheet2:

Sub MAIN()

ary = Array("Sheet1", "Sheet2")

Dim sh As Worksheet

For Each sh In Sheets(ary)

    sh.Activate

    Call I715KM

Next

End Sub

Answer 3

Thanks Gary,

Is it possible to simultaneiously add the rows in the other sheet?

Cheers

Answer 4

Here is an example for a single sheet.  It inserts two blank rows above row #715. This pushes row #715 down to row #717.  The macro goes to row #714 and copies each formula in that row down to the next two (newly inserted) rows:

Sub I715KM()

Range("715:715").Insert shift:=xlDown

Range("715:715").Insert shift:=xlDown

For Each r In Intersect(Range("714:714"), Cells.SpecialCells(xlCellTypeFormulas))

    r.Resize(3, 1).FillDown

Next

End Sub