Marco formatting

[JBoyer]

I set up the following Marco:

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

I also want it to adjust some of the cells that were shifted down five cells
to accomadate the new cells into the formula. These are the ones that need to
be adjusted:

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!

 

[Don Guillett]

try this idea

Sub InsertFiveRows()
Dim LR As Long
lr = Cells(Rows.Count, "A").End(xlUp).row
Rows(lr).Resize(5).Insert

'line above replaces 5 lines below
' 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(lr, "A")
Range("b47").Formula = "=COUNTA(B2:B" & lr + 4 & ")"
'etc for the others

End Sub

 

[JBoyer]

I tried your suggestion and it works for 2/4 of them. But even the two that
it works for it displays it in b47 when i would want it in b52. If you have
any other ideas or can tell me what I'm doing wrong I'd appreciate it. Thanks
for the fast response.

 

[Tom Ogilvy]

test this on a copy of your data:

Sub InsertFiveRows()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
v = Rows(LastRow - 1).Formula
Cells(LastRow - 1, "A").Resize(5, 1).EntireRow.Insert
Rows(LastRow - 1) = v
Rows(LastRow + 4).ClearContents
Range("AU1:BF5").Copy Cells(LastRow, "A")
End Sub

 

[JBoyer]

Sorry for the late feedback, but I just wanted to say that it now does
exactly what I want. Thank you for all your help!