How can I create a button in an excel sheet that copies its' respecive part of the sheet below it, and pastes it

Question

I have created an excel sheet for my company, which we use to calculate the prices of our products. We're not a high volume production company. We get 25 or 30 new projects every week, and those projects might have between 1 and 50 individual parts, maybe 2 or 3 of each.

So in this excel sheet template, I've copied and pasted about 10 of the same thing, which is a big grouping of different calculations for a single assembly.

This is what a single assembly group looks like:

You can see that there are 3 sheet metal sections, 3 extrusion lines, 3 purchased parts, etc...

What I need is a few buttons. Maybe on the header of the assembly group, maybe at the bottom of each section individually.

This button just needs to insert another new, blank copy of one of those sections. So if I click the "Add sheet metal" button, a new, blank sheet metal section gets added under the very last sheet metal section, and pushes the rest of the sheet down.

I've looked for months for a way to do this. Copying and inserting the copied cells is sometimes buggy, as totals and references get messed up sometimes.

Anyone know how to do this?

Answer 1

Here's a sample macro that does what you're looking for. This example assumes that the data you want to replicate is in cells A3 thru E4, but you can change this to the cells relevant to you.

Sub InsertSection()
    Range("A3:E4").Select  ' change this to the cells relevant to you
    Selection.Copy
    Selection.Insert Shift:=xlDown
End Sub

You can then assign this macro to a button. Here's the code in action with a button I made:

When you click the button, the macro does the following: