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!

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JBoyer" wrote in message
...
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!

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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JBoyer" wrote in message
...
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!

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

--
Regards,
Tom Ogilvy


"JBoyer" wrote:

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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JBoyer" wrote in message
...
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!

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!

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"JBoyer" wrote:

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.

"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JBoyer" wrote in message
...
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!