VBA Code/Macro to insert rows into a protected sheet and copy formulas down

[grahamlees]

Hi,

I am very new to VBA and Macros and to be honest new to all the extended functionality in Excel. What I am trying to achieve

I have created a table in excel and the first column is based on a drop down list in a separate sheet. There are approximately 8 items in the list and each one has adjacent cells that has data I want to copy through to the main table and auto fill certain fields.

I have successfully managed to do the first step using Vlookup.

I also want to protect my worksheet to stop people changing the formulas and values in the auto filled cells on the main table and allow people to add additional rows, select from the drop down in column A and auto fill the cells when the work book is protected. This is the part I am now stuck with.

Does any one have any examples how to achieve this using VBA/Macro.

Ideally what I would like is the following:

- Add a button on the sheet that allows me to insert a new row in the protected sheet ideally where the cursor is on the sheet
- Ensure the formula and drop down list is copied into the new row from the above rows
- Select an item from the drop down list and depending on the contents auto fill the adjacent cells.

I have attached what I have done so far if this helps.

Hope someone can help. I am also new to the VBA editor as well so some explanation might be required as well

[AliGW]

I was lucky enough to be given help on a similar query some time ago and have managed to adapt the code I was given to work on your data.

Create a new macro from the developer ribbon and copy this into the window:

Please Login or Register  to view this content.

Now close the VBA window and run the macro on your protected sheet - a new row should be added at the bottom of the table. You can now assign this macro to a command button on your worksheet.

[grahamlees]

Hello and sorry for being cheeky but could you show me how to modify the code so it will delete a row as well

Really Appreciate your help

Thanks Graham

[AliGW]

Thanks for the rep. If you need any help adapting it, you'll need one of the VBA experts, but if that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

EDIT: just seen your second query. Hang on - I think I've got that, too!

[AliGW]

Try this:

Please Login or Register  to view this content.

If you run into any trouble, you'll need a VBA expert!