How can I insert a row and have it also continue the formulas?
I found the following but not sure if this is right or if so how to adapt it.
My formatted table is A1:AQ74 with the following formulas
Excel 2007
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]11[/TD]
[TD="bgcolor: #F0AB00, align: center"]Oct '12[/TD]
[TD="bgcolor: #F0AB00, align: center"]Patient [/TD]
[TD="bgcolor: #F0AB00, align: center"]Referral [/TD]
[TD="bgcolor: #F0AB00, align: center"]Referral[/TD]
[TD="bgcolor: #F0AB00, align: center"]Referral[/TD]
[TD="bgcolor: #F0AB00, align: center"]Referral[/TD]
[TD="bgcolor: #F0AB00, align: center"]Case Mgr[/TD]
[TD="bgcolor: #F0AB00"]Financial Class[/TD]
[TD="bgcolor: #F0AB00"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Diagnosis[/TD]
[TD="bgcolor: #F0AB00, align: center"]Evaluate[/TD]
[TD="bgcolor: #F0AB00, align: center"]Denied /[/TD]
[TD="bgcolor: #F0AB00, align: center"]Code[/TD]
[TD="bgcolor: #F0AB00, align: center"]Reason[/TD]
[TD="bgcolor: #F0AB00, align: center"]Activity[/TD]
[TD="bgcolor: #F0AB00, align: center"]Discharge[/TD]
[TD="bgcolor: #F0AB00, align: center"]Discharge[/TD]
[TD="bgcolor: #F0AB00, align: center"]Admit[/TD]
[TD="bgcolor: #F0AB00, align: center"]Admit[/TD]
[TD="bgcolor: #F0AB00, align: center"]PND[/TD]
[TD="bgcolor: #F0AB00, align: center"]PND[/TD]
[TD="bgcolor: #F0AB00, align: center"]D/C[/TD]
[TD="bgcolor: #F0AB00, align: center"]Phy[/TD]
[TD="bgcolor: #F0AB00, align: center"]Med[/TD]
[TD="bgcolor: #F0AB00, align: center"]SNFLR[/TD]
[TD="bgcolor: #F0AB00, align: center"]UNIT[/TD]
[TD="bgcolor: #F0AB00, align: center"]AMERI[/TD]
[TD="bgcolor: #F0AB00, align: center"]United[/TD]
[TD="bgcolor: #F0AB00, align: center"]BCBS[/TD]
[TD="bgcolor: #F0AB00, align: center"]Coventry[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Other[/TD]
[TD="bgcolor: #F0AB00, align: center"]No[/TD]
[TD="bgcolor: #F0AB00, align: center"]No[/TD]
[TD="bgcolor: #F0AB00, align: center"]No[/TD]
[TD="bgcolor: #F0AB00"]Transport Delay[/TD]
[TD="bgcolor: #F0AB00"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Dr Approval[/TD]
[TD="bgcolor: #F0AB00, align: center"]Other[/TD]
[TD="bgcolor: #F0AB00, align: center"]Total Delay Time [/TD]
[TD="bgcolor: #F0AB00, align: center"](W)ithin 48 hrs /[/TD]
[TD="bgcolor: #F0AB00, align: center"]Total Hours from[/TD]
[TD="align: center"]12[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Name[/TD]
[TD="bgcolor: #F0AB00, align: center"]Facility[/TD]
[TD="bgcolor: #F0AB00, align: center"]Date/Time[/TD]
[TD="bgcolor: #F0AB00, align: center"]Day[/TD]
[TD="bgcolor: #F0AB00, align: center"]Doctor[/TD]
[TD="bgcolor: #F0AB00, align: center"]RN[/TD]
[TD="bgcolor: #F0AB00, align: center"]Primary[/TD]
[TD="bgcolor: #F0AB00, align: center"]Secondary[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Date[/TD]
[TD="bgcolor: #F0AB00, align: center"]Void ?[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Level[/TD]
[TD="bgcolor: #F0AB00, align: center"]Rec[/TD]
[TD="bgcolor: #F0AB00, align: center"]Actual[/TD]
[TD="bgcolor: #F0AB00, align: center"]Date/Time[/TD]
[TD="bgcolor: #F0AB00, align: center"]Day[/TD]
[TD="bgcolor: #F0AB00, align: center"]OT #[/TD]
[TD="bgcolor: #F0AB00, align: center"]PT #[/TD]
[TD="bgcolor: #F0AB00, align: center"]Delay[/TD]
[TD="bgcolor: #F0AB00, align: center"]Delay[/TD]
[TD="bgcolor: #F0AB00, align: center"]Hold[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Head Bed[/TD]
[TD="bgcolor: #F0AB00, align: center"]Bed[/TD]
[TD="bgcolor: #F0AB00, align: center"]Bari Bed[/TD]
[TD="bgcolor: #F0AB00, align: center"]Pt. Not Ready[/TD]
[TD="bgcolor: #F0AB00, align: center"]Pt. Appt.[/TD]
[TD="bgcolor: #F0AB00, align: center"]Delay[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Hours:Minutes[/TD]
[TD="bgcolor: #F0AB00, align: center"](N)ot within 48 hrs[/TD]
[TD="bgcolor: #F0AB00, align: center"]Ref. to Adm.[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Jane Doe[/TD]
[TD="align: center"]VCH-F[/TD]
[TD="align: center"]1/13/13 10:00 AM[/TD]
[TD="align: center"]Sunday[/TD]
[TD="align: center"]Grundmeyer[/TD]
[TD="align: center"]Joan[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]BC[/TD]
[TD="align: center"]CVA[/TD]
[TD="align: center"]1/4/13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/16/13 1:00 PM[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]0:12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3:30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3:42[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]30[/TD]
</tbody>
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E13[/TH]
[TD="align: left"]=IF(D13="","",TEXT(D13,"dddd"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]S13[/TH]
[TD="align: left"]=IF(R13="","",TEXT(R13,"dddd"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AO13[/TH]
[TD="align: left"]=IF(SUBTOTAL(109,T13:AN13)=0,"",(SUBTOTAL(109,T13:AN13)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AP13[/TH]
[TD="align: left"]=IF(D13="","",IF($F$7<=R13-D13,"W","N"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AQ13[/TH]
[TD="align: left"]=IF(R13="","",IF(AND(INT(D13)=INT(R13),NOT(ISNA(MATCH(INT(D13),$A$3:$F$3,0)))),0,ABS(IF(INT(D13)=INT(R13),ROUND(24*(R13-D13),2),(24*($B$6-$A$6)*(MAX(NETWORKDAYS(D13+1,R13-1,$A$3:$F$3),0)+INT(24*(((R13-INT(R13))-(D13-INT(D13)))+($B$6-$A$6))/(24*($B$6-$A$6))))+MOD(ROUND(((24*(R13-INT(R13)))-24*$A$6)+(24*$B$6-(24*(D13-INT(D13)))),2),ROUND((24*($B$6-$A$6)),2)))))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
This is the code I found for inserting a row....
I found the following but not sure if this is right or if so how to adapt it.
My formatted table is A1:AQ74 with the following formulas
Excel 2007
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | AG | AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]11[/TD]
[TD="bgcolor: #F0AB00, align: center"]Oct '12[/TD]
[TD="bgcolor: #F0AB00, align: center"]Patient [/TD]
[TD="bgcolor: #F0AB00, align: center"]Referral [/TD]
[TD="bgcolor: #F0AB00, align: center"]Referral[/TD]
[TD="bgcolor: #F0AB00, align: center"]Referral[/TD]
[TD="bgcolor: #F0AB00, align: center"]Referral[/TD]
[TD="bgcolor: #F0AB00, align: center"]Case Mgr[/TD]
[TD="bgcolor: #F0AB00"]Financial Class[/TD]
[TD="bgcolor: #F0AB00"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Diagnosis[/TD]
[TD="bgcolor: #F0AB00, align: center"]Evaluate[/TD]
[TD="bgcolor: #F0AB00, align: center"]Denied /[/TD]
[TD="bgcolor: #F0AB00, align: center"]Code[/TD]
[TD="bgcolor: #F0AB00, align: center"]Reason[/TD]
[TD="bgcolor: #F0AB00, align: center"]Activity[/TD]
[TD="bgcolor: #F0AB00, align: center"]Discharge[/TD]
[TD="bgcolor: #F0AB00, align: center"]Discharge[/TD]
[TD="bgcolor: #F0AB00, align: center"]Admit[/TD]
[TD="bgcolor: #F0AB00, align: center"]Admit[/TD]
[TD="bgcolor: #F0AB00, align: center"]PND[/TD]
[TD="bgcolor: #F0AB00, align: center"]PND[/TD]
[TD="bgcolor: #F0AB00, align: center"]D/C[/TD]
[TD="bgcolor: #F0AB00, align: center"]Phy[/TD]
[TD="bgcolor: #F0AB00, align: center"]Med[/TD]
[TD="bgcolor: #F0AB00, align: center"]SNFLR[/TD]
[TD="bgcolor: #F0AB00, align: center"]UNIT[/TD]
[TD="bgcolor: #F0AB00, align: center"]AMERI[/TD]
[TD="bgcolor: #F0AB00, align: center"]United[/TD]
[TD="bgcolor: #F0AB00, align: center"]BCBS[/TD]
[TD="bgcolor: #F0AB00, align: center"]Coventry[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Other[/TD]
[TD="bgcolor: #F0AB00, align: center"]No[/TD]
[TD="bgcolor: #F0AB00, align: center"]No[/TD]
[TD="bgcolor: #F0AB00, align: center"]No[/TD]
[TD="bgcolor: #F0AB00"]Transport Delay[/TD]
[TD="bgcolor: #F0AB00"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Dr Approval[/TD]
[TD="bgcolor: #F0AB00, align: center"]Other[/TD]
[TD="bgcolor: #F0AB00, align: center"]Total Delay Time [/TD]
[TD="bgcolor: #F0AB00, align: center"](W)ithin 48 hrs /[/TD]
[TD="bgcolor: #F0AB00, align: center"]Total Hours from[/TD]
[TD="align: center"]12[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Name[/TD]
[TD="bgcolor: #F0AB00, align: center"]Facility[/TD]
[TD="bgcolor: #F0AB00, align: center"]Date/Time[/TD]
[TD="bgcolor: #F0AB00, align: center"]Day[/TD]
[TD="bgcolor: #F0AB00, align: center"]Doctor[/TD]
[TD="bgcolor: #F0AB00, align: center"]RN[/TD]
[TD="bgcolor: #F0AB00, align: center"]Primary[/TD]
[TD="bgcolor: #F0AB00, align: center"]Secondary[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Date[/TD]
[TD="bgcolor: #F0AB00, align: center"]Void ?[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Level[/TD]
[TD="bgcolor: #F0AB00, align: center"]Rec[/TD]
[TD="bgcolor: #F0AB00, align: center"]Actual[/TD]
[TD="bgcolor: #F0AB00, align: center"]Date/Time[/TD]
[TD="bgcolor: #F0AB00, align: center"]Day[/TD]
[TD="bgcolor: #F0AB00, align: center"]OT #[/TD]
[TD="bgcolor: #F0AB00, align: center"]PT #[/TD]
[TD="bgcolor: #F0AB00, align: center"]Delay[/TD]
[TD="bgcolor: #F0AB00, align: center"]Delay[/TD]
[TD="bgcolor: #F0AB00, align: center"]Hold[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Head Bed[/TD]
[TD="bgcolor: #F0AB00, align: center"]Bed[/TD]
[TD="bgcolor: #F0AB00, align: center"]Bari Bed[/TD]
[TD="bgcolor: #F0AB00, align: center"]Pt. Not Ready[/TD]
[TD="bgcolor: #F0AB00, align: center"]Pt. Appt.[/TD]
[TD="bgcolor: #F0AB00, align: center"]Delay[/TD]
[TD="bgcolor: #F0AB00, align: center"][/TD]
[TD="bgcolor: #F0AB00, align: center"]Hours:Minutes[/TD]
[TD="bgcolor: #F0AB00, align: center"](N)ot within 48 hrs[/TD]
[TD="bgcolor: #F0AB00, align: center"]Ref. to Adm.[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Jane Doe[/TD]
[TD="align: center"]VCH-F[/TD]
[TD="align: center"]1/13/13 10:00 AM[/TD]
[TD="align: center"]Sunday[/TD]
[TD="align: center"]Grundmeyer[/TD]
[TD="align: center"]Joan[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]BC[/TD]
[TD="align: center"]CVA[/TD]
[TD="align: center"]1/4/13[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/16/13 1:00 PM[/TD]
[TD="align: center"]Wednesday[/TD]
[TD="align: center"]0:12[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3:30[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3:42[/TD]
[TD="align: center"]W[/TD]
[TD="align: center"]30[/TD]
</tbody>
Nov 12
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #E0E0F0"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]E13[/TH]
[TD="align: left"]=IF(D13="","",TEXT(D13,"dddd"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]S13[/TH]
[TD="align: left"]=IF(R13="","",TEXT(R13,"dddd"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AO13[/TH]
[TD="align: left"]=IF(SUBTOTAL(109,T13:AN13)=0,"",(SUBTOTAL(109,T13:AN13)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AP13[/TH]
[TD="align: left"]=IF(D13="","",IF($F$7<=R13-D13,"W","N"))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #E0E0F0"]AQ13[/TH]
[TD="align: left"]=IF(R13="","",IF(AND(INT(D13)=INT(R13),NOT(ISNA(MATCH(INT(D13),$A$3:$F$3,0)))),0,ABS(IF(INT(D13)=INT(R13),ROUND(24*(R13-D13),2),(24*($B$6-$A$6)*(MAX(NETWORKDAYS(D13+1,R13-1,$A$3:$F$3),0)+INT(24*(((R13-INT(R13))-(D13-INT(D13)))+($B$6-$A$6))/(24*($B$6-$A$6))))+MOD(ROUND(((24*(R13-INT(R13)))-24*$A$6)+(24*$B$6-(24*(D13-INT(D13)))),2),ROUND((24*($B$6-$A$6)),2)))))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
This is the code I found for inserting a row....
Code:
Sub Add_Row_and_Formula()
Dim insRows As Long
insRows = ActiveCell.Row
Selection.Insert Shift:=xlDown
Range("AB" & insRows - 1).Select
Selection.AutoFill Destination:=Range _
("AB" & insRows - 1, "AB" & insRows), Type:=xlFillValues
Range("AB" & insRows - 1, "AB" & insRows).Select
Range("AB" & insRows).Select
End Sub