[Urgent] How to add Row Below or above Automatically

[funtonight]

Hi Friends,
i need lil help regarding in following sample how to add row automatically below row 4 means above the 5th row named total , and it continues to add row above TOTAL when the above row of Total gets filled . like at the moment row 4 is filled then i want excel to add row 5 automatically and Total row moves to row 6 but the formulas remain intact of total and so on

Sample of Excel Sheet am using ( IN THIS POST FORMATTING IS NOT REMAINING INTACT BUT HOPE YOU WILL UNDERSTAND:



<table border="0" cellpadding="0" cellspacing="0" width="320"><col style="width: 48pt;" span="5" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 48pt;" height="20" width="64">Date</td> <td style="width: 48pt;" width="64">C.V No </td> <td style="width: 48pt;" width="64">Payment</td> <td style="width: 48pt;" width="64">Recipts</td> <td style="width: 48pt;" width="64">Comments</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">1/6/2011</td> <td align="right">5</td> <td align="right">50000</td> <td>
</td> <td>payment</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl65" style="height: 15pt;" align="right" height="20">7/6/2011</td> <td align="right">17</td> <td>
</td> <td align="right">1000</td> <td>recipt</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">13-6-2011</td> <td align="right">16</td> <td align="right">5000</td> <td>
</td> <td>POL</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">14-6-2011</td> <td align="right">18</td> <td>
</td> <td align="right">600</td> <td>OTHERS</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">
</td> <td>Total</td> <td align="right">55000</td> <td align="right">1600</td> <td>
</td> </tr> </tbody></table>

i dont know much abt Macro , so please tell me in simple language and step by step procedure how to add that code to make it work

Regards!

 

[Maheshp]

sorry

Right click on sheet tab & click view code, paste below code in sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Found As Range
Set Found = Columns(1).Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing And Found.Offset(-1, 0).Rows.Value <> "" Then Found.EntireRow.Insert
End Sub

 

[funtonight]

hEY THanks i made it work now i wana know is that how to apply this to WHOLE WORK SHEET instead of just that sheet?

 

[funtonight]

and One last question:

if in new row i dont write a figure say in Payment colum and den add a new row and der i add value in payment section den it DONT UPDATE FORMULA


means at the moment its necessaary to add value in every colum only den the TOTAL Sum formula gets updated


can it be sorted out ?

 

[funtonight]

Plus it is adding new row above total colum


but den again after that total down in rows ,in another colum i wrote Total but der this formula is not getting applied and it is not adding new row above it so help

 

[Maheshp]

Press Alt + F11
Right click on Thisworkbook select view code & copy the above code to apply it whole workbook

suppose your "Total" word is in column A at "A24" against that there is sum of "Payment" & "Recipts", sum range =SUM(B2:B12) in column B
when you add the new row sum range will change automatically

 

[Maheshp]

try below code in thisworkbook module not above

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Found As Range
Set Found = Columns(1).Find(what:="Total", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing And Found.Offset(-1, 0).Rows.Value <> "" Then Found.EntireRow.Insert
End Sub

 

[funtonight]

Hi thanks above code is working but its not preserving formating as in row borders when adding new row
so can any one modify it >?

 

[Maheshp]

Hi
select you data from "total" word row & then apply border ,whenver it add new row u will get border

<TABLE style="WIDTH: 236pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=313><COLGROUP><COL style="WIDTH: 55pt; mso-width-source: userset; mso-width-alt: 2669" width=73><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 55pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=73>Date </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 39pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=52>C.V No </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=62>Payment</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 39pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=52>Recipts</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=74>Comments</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 align=right>21-06-2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>POL</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 align=right>21-06-2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>5000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Total</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>10000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 align=right>1000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63> </TD></TR></TBODY></TABLE>

 

[funtonight]

Hi thanks Maheshp... you have been realy helpful