Insert Row copy formulas but not content

[lianneval]

Hi,

I am a bit rusty on excel and wanted to check how you would suggest I complete the below.

I have a spreadsheet with various sections so for example Rows 3 to 6 are one section, rows 7 to 10 are another and 11 to 14 are another. I want to add a button to click in each section to add an additional row, formatted the same as the one above and copying the formula from column A but no other content. So for example you could click a button which would create a new row 7 formatted the same as rows 3 to 6 then click again to create row 8 the same with the formula in column 8 being replicated.

I've managed to do it to create one row but can't replicate it to click again and create another.

Thank you in advance for any help

 

[Joe4]

Re: Insert Row copy forumlas but not content

Welcome to the Board!

Why don't you post the code that you have now, and we can help you modify it to do what you want.

 

[lianneval]

Re: Insert Row copy forumlas but not content

Thanks, this is what I have so far, basically the first colum is numbered, 1, 2, 3 etc using a formula to be 1 above the previous, so it creates what would be considered row 4 and labels it 4 but then rows 5, 6,7 etc are all also just 4. Hope that makes sense

Sub AddARow()
Rows("50:50").Insert Shift:=x1Down
Rows("49:49").Copy
Rows("50:50").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone

End Sub

 

[Joe4]

Re: Insert Row copy forumlas but not content

How is it to know where it should be inserting the new row?
Is it always going to be adding a new row after the last entry in column A (in which case, you wouldn't need to insert a new row, you could simply copy)?
Or is there some other way of determining exactly where to insert this new row (if so, what)?

 

[lianneval]

Re: Insert Row copy forumlas but not content

I think that's perhaps my problem. Because it is a bit like a form (without being a form) and I want to be able to add a new row in each section it is not the last row where I want it inserted so its' hard to reference. So for example they'll be data in rows 47,48 and 49 then I want the option of adding an extra row here if necessary via a button. Then they'll be different data in rows 50, 51 and 52 where I want to replicate the button to add another row here if needed. Perhaps I'd be better just creating a form.

 

[Joe4]

Re: Insert Row copy forumlas but not content

I want the option of adding an extra row here if necessary via a button.

It is definitely do-able in VBA, but we just need to figure out how to define exactly where "here" is.
That is, how do we tell VBA which "block" of data it should update?

Some options include:
- It could be based on the Active Cell at the time of running
- You could prompt the user to enter the block or row where it is to be inserted
- Or some other methodology that you come up with

If you tell us how to identify where "here" is, we can program for it.