Add a row, copy formulas, and formatting

I'm new to this forum and new to Excel Macros/VBA and am totally frustrated.

In my Excel spreadsheet I want to have a button for other users to click that will (1) add a new row in a specific location (right now the code I have will add a new row anywhere the user clicks), (2) copy the formulas (but not the data that has been entered), (3) copy the formatting and (4) number the items in the far left column increasing by 1 each time a new row is added.

I can find various vba for inserting a new row (but not in a specified spot), I can find how to add numbers by 1 in rows and I have found vba code for copying formatting. But not all in one code so that the 1 action of clicking the button will perform these 4 functions.

Any help would be so greatly appreciated. Thanks.

The code for adding the new row that I have (but will insert it where ever the user has clicked) is:

Please Login or Register  to view this content.

However, I found this formula in this forum, but it copies the data the user may have input:

Please Login or Register  to view this content.

Hello sp135,

Welcome to the Forum!

Your post title should convey a brief description of what you need help with. Please do not use general words and phrases like "Help!", "Code doesn't work", "Urgent!", "Should be an easy question", "Is this possible?", etc. Your present title doesn't tell anyone what your question is or what type of problems you are experiencing.
A better title would be "Add a row, copy data, formulas, and formatting".

As a new member, please take the time to read over the Forum Rules .

Hi sp35,

welcome to the forum. Please take a look at the forum rules, then amend your thread title and add code tags.

thanks

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Hello sp135,

Thanks for changing the title. I added the code tags to your post so you can see what Teylyn was referring to. Please do this in future posts.

Do you need to copy formulae down once the rows are added? Will the sequence numbers be in column "A"?

Hello sp135,

I have created an example workbook. This has a button below the first entry on row 8. Clicking the button will add a new row. There are named ranges on the sheet "Named Ranges" for the drop down validation and the starting sequence number. This is computed by subtracting 7 from the current entry row. Since the first entry row is 8, the line item number equals 1. Here is the macro called by the button.

Please Login or Register  to view this content.

This worked for me. (Although I had to make my own test book. My Mac doesn't play well with ActiveX controls)

Please Login or Register  to view this content.