if Insertion of new rows in sheet 1 with copying formula from above cell, then same to Sheet 2

[Tet Htut Naing]

Hi,
I have a big question of insertion of new rows.
I have excel book, in which 4 sheets have data of staff of my organization. But all of the staff name are put in column B of all sheets, especially with same numbering. For example,
[TABLE="width: 168"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]1.[/TD]
[TD]Tet Htut Naing[/TD]
[/TR]
[TR]
[TD]2.[/TD]
[TD]Moe Thar[/TD]
[/TR]
[TR]
[TD]3.[/TD]
[TD]Myat Thu[/TD]
[/TR]
[TR]
[TD]4.
[/TD]
[TD]Myat Ko
[/TD]
[/TR]
</tbody>[/TABLE]
These numbers and names are identically the same in all sheets. I use the formula of =IF(TRIM(B4)<>"",COUNTA($B$4:B4)&".","") in Column A, whenever I put a name in column B, then it will automatically numbering in Column B. That is simple.

But one of the staff is to delete in sheet 1, I need a macro for deleting the same row in sheet 2, 3 and 4.
Again, more complecated, maybe, if a row is inserted in-between number 2 and 3 in Sheet 1 for new staff, then I need two things:
1. In those row insertions, fill always the formula for numbering in Column A in sheet 1.
2. Moreover, In sheet 2, 3 and 4, there will be formula for linking with Sheet 1, to auto appearing of Staff name; I need it with relative reference. For example, in sheet 2, row number 1, Column B, I put this - =Sheet 1!B1. That formula is not working relative referencing of new insertion.

Is it possible to do so with macros. I tried several times with record macros with relative reference, it always ends with errors.

Please Help me!!!

 

[sijpie]

This should do the trick. Note: you need to use your sheet names: at the top you see where i have set the four sheetnames to Staff1, etc, change Staff1, Staff2 etc to the sheet names you use, where Staff1 is the main sheet


<font face=Calibri><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#007F00">'>>>> Modify these names to suit your sheet names <<<<</SPAN><br><SPAN style="color:#00007F">Const</SPAN> sSHEET1NAME = "Staff1"<br><SPAN style="color:#00007F">Const</SPAN> sSHEET2NAME = "Staff2"<br><SPAN style="color:#00007F">Const</SPAN> sSHEET3NAME = "Staff3"<br><SPAN style="color:#00007F">Const</SPAN> sSHEET4NAME = "Staff4"<br><br><SPAN style="color:#007F00">'--------------------------------------------------------</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> InsertLine()<br><SPAN style="color:#007F00">' Insert a line in all Staff worksheets and copy formulas</SPAN><br><SPAN style="color:#007F00">'</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rR <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsWS <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> mbaAnsw <SPAN style="color:#00007F">As</SPAN> VbMsgBoxResult<br>    <br>    lR = ActiveCell.Row<br>    mbaAnsw = MsgBox("Do you want to insert a line above " & Cells(lR, 2) & " current position?", _<br>                    Buttons:=vbOKCancel + vbQuestion, _<br>                    Title:="Insert New Line")<br>                <br>    <SPAN style="color:#00007F">If</SPAN> mbaAnsw = vbOK <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wsWS <SPAN style="color:#00007F">In</SPAN> Worksheets<br>            <SPAN style="color:#00007F">With</SPAN> wsWS<br>                <SPAN style="color:#007F00">' only insert in the four staff sheets</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> .Name = sSHEET1NAME <SPAN style="color:#00007F">Or</SPAN> .Name = sSHEET2NAME Or _<br>                        .Name = sSHEET3NAME <SPAN style="color:#00007F">Or</SPAN> .Name = sSHEET4NAME <SPAN style="color:#00007F">Then</SPAN><br>                    <SPAN style="color:#007F00">' insert a row</SPAN><br>                    .Cells(lR, 1).EntireRow.Insert<br>                    <br>                    <SPAN style="color:#00007F">If</SPAN> lR > 4 <SPAN style="color:#00007F">Then</SPAN>  <SPAN style="color:#007F00">' copy formulas from row above</SPAN><br>                        .Range(.Cells(lR - 1, 1), .Cells(lR, 2)).FillDown<br>                    <br>                    <SPAN style="color:#00007F">Else</SPAN>    <SPAN style="color:#007F00">' first row, copy from row below</SPAN><br>                        .Cells(lR, 1).Formula = _<br>                                "=IF(TRIM(B4)<>"""",COUNTA($B$3:B4)-1&""."","""")"<br>                        <SPAN style="color:#00007F">If</SPAN> .Name <> sSHEET1NAME <SPAN style="color:#00007F">Then</SPAN><br>                            .Cells(lR, 2).Formula = "='Staff1'!B" & lR<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                    <br>                    <SPAN style="color:#00007F">If</SPAN> .Name = sSHEET1NAME <SPAN style="color:#00007F">Then</SPAN>     <SPAN style="color:#007F00">' first sheet, delete duplicated name</SPAN><br>                        Cells(lR, 2).ClearContents<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> wsWS<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#007F00">'--------------------------------------------------------</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> DeleteLine()<br><SPAN style="color:#007F00">' Delete a line in all staff worksheets</SPAN><br><SPAN style="color:#007F00">'</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rR <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> lR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> wsWS <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> mbaAnsw <SPAN style="color:#00007F">As</SPAN> VbMsgBoxResult<br>    <br>    lR = ActiveCell.Row<br>    mbaAnsw = MsgBox("Do you want to delete the employee: " & Cells(lR, 2) & " at current line?", _<br>                    Buttons:=vbOKCancel + vbQuestion, _<br>                    Title:="Delete Employee " & Cells(lR, 2))<br>                <br>    <SPAN style="color:#00007F">If</SPAN> mbaAnsw = vbOK <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> wsWS <SPAN style="color:#00007F">In</SPAN> Worksheets<br>            <SPAN style="color:#00007F">With</SPAN> wsWS<br>                <SPAN style="color:#007F00">' only delete from the four staff sheets</SPAN><br>                <SPAN style="color:#00007F">If</SPAN> .Name = sSHEET1NAME <SPAN style="color:#00007F">Or</SPAN> .Name = sSHEET2NAME Or _<br>                        .Name = sSHEET3NAME <SPAN style="color:#00007F">Or</SPAN> .Name = sSHEET4NAME <SPAN style="color:#00007F">Then</SPAN><br>                    .Cells(lR, 1).EntireRow.Delete<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> wsWS<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>


let me know how you get on.

 

[Tet Htut Naing]

Dear sijpie,
I really thank you with all my heart. It really works, PERFECTLY. Well done. Great Job.
One thing I want to request: let me share this code to other excel forum, but only with your name, and this particular link, quoted, and honoring you, especially.
The intention is only to make happy people who face this kind of problem. There are several people on other sites, searching this code.
I am waiting your response.
I will stay in this site, for my excel problem.
Congratulations Mr. Sijpie.

Best Regards,
Htut

 

[sijpie]

Yes that is perfectly alright.

Glad it worked.