master sheet updated itself with new added sheets

Dear Friends,

Hello,


I am very happy with this forum and now adding a new task

I have attached a master sheet where I want that it updates column A and column B of its own whenever a new worksheet is added( column A contains Name of person and column B contains Fathers Name) i.e whenever the new worksheet is added the master sheets should get updated of its own like the old names should remain there and new names in column A and B should get added below the old ones. I hope I am able to explained every thing..........

Manoj Sharma

Maybe this little trick will help you.

if you add a blank worksheet at the start and end of your data sheets, and call them Start and End, then when you enter your calcs, you could just enter =sum('start:End'!C12) or whatever your references are. this way, you can add or remove sheets as needed, and it will add across all the sheets you need it to, and keep the totals as the data sheets change. make sure your "Master" sheet is before the Start sheet though

well friend FDibbins

am bit confused I want that the person name in column A & fathers name in column B should be updated but after putting this formula it is showing numerical number, can you please help me out.......thanks

hi Manoj. VBA is not really my forte, so i did this with a little recording of removing duplicates.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

if you want to run it automatically, i'm not sure if it would make your workbook slow. you could skip the step of creating a Module & double-click the Sheet1 (master sheet) on the left panel & paste this code inside:

Please Login or Register  to view this content.

the names are permanently in master sheet once you put a number in column A

thanks my friend Benishiryo- for your reply,

This will be my first attempt with VBA so let me try and hope every thing goes well because you have explained it so well. So I try it and get back to you for any further help. Once again thank you so much for helping me. God Bless You.