Marco

[JBoyer]

I know what I marco is, but I am not at all familiar with using them. Here is
my dilemma. I have a worksheet that needs to be updated by adding new cells
above a row of totals and below the last set of formulas. Use this for an
example. Lets say a1=f1*8, a2=f2*8, a3=f3*8, a4=f4*8; and b1=g1*8, b2=g2*8,
b3=g3*8, b4=g4*8. Underneath them in a5 and b5 would be a sum function for
example. What a want to do is insert two rows above a5 and b5, so the sum
function will now be in a7 and b7. Then copy cells a1:b2 and paste them in
a5:b6. If i would use the marco again it would insert two more rows and copy
cells a1:b2 and paste them in a7:b8 and have the sum function be pushed down
to a9 and b9. Not sure if this is even possible, or if a marco will work for
it, but any help is appreciated!

 

[Rick Rothstein \(MVP - VB\)]

This will do what you asked, but it must be run with the worksheet you want
to use it on active (that is, go to the worksheet you want to use this on,
press Alt+F8, select InsertTwoDataRows from the list and click the Run
button)...

Sub InsertTwoDataRows()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(LastRow, "A").EntireRow.Insert
Cells(LastRow, "A").EntireRow.Insert
Range("1:2").Copy Cells(LastRow, "A")
End Sub

Rick

 

[Tom Ogilvy]

I assume you want the formula in the Sum cells adjusted to cover the old
range plus the added cells:

Sub InsertTwoRows()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(LastRow, "A").Resize(2, 2).Insert Shift:=xlShiftDown
Range("A1:B2").Copy Cells(LastRow, "A")
Cells(LastRow + 2, "A").Resize(1, 2).FormulaR1C1 = _
"=SUM(R1C:R[-1]C)"
End Sub

 

[JBoyer]

This is how I modified your formula to fit my sheet. However you are right I
need to have the last row change to accomadate for the newly added cells,
however my formulas are more complicated than a simple sum function so I hope
you can help.

Sub InsertFiveRows()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(LastRow, "A").EntireRow.Insert
Cells(LastRow, "A").EntireRow.Insert
Cells(LastRow, "A").EntireRow.Insert
Cells(LastRow, "A").EntireRow.Insert
Cells(LastRow, "A").EntireRow.Insert
Range("AU1:BF5").Copy Cells(LastRow, "A")
End Sub

Cell B47 currently has the formula =COUNTA(B2:B46)
Cell C47 currently has the formula
=IF(SUMIF(E2:E46,">0",C2:C46)>0,SUMIF(E2:E46,">0",C2:C46),"")
Cell D47 currently has the formula =IF(C47<>"",E47/C47,"")
Cell E47 currently has the formula =IF(SUM(E2:E46)>0,SUM(E2:E46),"")

All of these cells need to accomadate 5 newly added rows. Hope you can help,
and thanks for the fast replies last time.

I assume you want the formula in the Sum cells adjusted to cover the old
range plus the added cells:

Sub InsertTwoRows()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(LastRow, "A").Resize(2, 2).Insert Shift:=xlShiftDown
Range("A1:B2").Copy Cells(LastRow, "A")
Cells(LastRow + 2, "A").Resize(1, 2).FormulaR1C1 = _
"=SUM(R1C:R[-1]C)"
End Sub

--
Regards,
Tom Ogilvy

I know what I marco is, but I am not at all familiar with using them. Here is
my dilemma. I have a worksheet that needs to be updated by adding new cells
above a row of totals and below the last set of formulas. Use this for an
example. Lets say a1=f1*8, a2=f2*8, a3=f3*8, a4=f4*8; and b1=g1*8, b2=g2*8,
b3=g3*8, b4=g4*8. Underneath them in a5 and b5 would be a sum function for
example. What a want to do is insert two rows above a5 and b5, so the sum
function will now be in a7 and b7. Then copy cells a1:b2 and paste them in
a5:b6. If i would use the marco again it would insert two more rows and copy
cells a1:b2 and paste them in a7:b8 and have the sum function be pushed down
to a9 and b9. Not sure if this is even possible, or if a marco will work for
it, but any help is appreciated!