Macro for button, insert row above button, copy formulas and formatting from row above

Hello TheBullND,

Welcome to the Forum.

Please see if you could use the attached sample Workbook.

Regards

Hi TheBullND,

I have some code you might be able to use. This also has the ability to insert a specified number of rows that are copied, basically repeating the copy and insert as many times as you specify. B1 is the cell containing the number of copies to produce in the example below.

Just change the ranges:

Range("A12:AW12").Copy
Range("A15:AW15").Select

To the ranges where you need to copy from and where to insert the copied range.

Please Login or Register  to view this content.

Hello TheBullND,

Thank you for the feedback.

I would need to see what your sheet layout is like and where you have Formulas, since you cannot ClearContents with clearing out formulas as well. We will have to reference specific Cells to retain Formulae.

Please upload a sample Workbook.

Regards

To additionaly cleaar the contents of the range copied, change Range("A12:AW12").Select to the range you are copying.

Please Login or Register  to view this content.

And the reason I want it in reference to the "button" is because when you press the "button" and it inserts the row above the button and below the previous row, the button will shift down a row.
Similar to this:

Please Login or Register  to view this content.

But its not copying down the previous row, etc.

I think you might want to work with Winon for the clearing of contents as my suggestion will clear the formulae too.

Actually...getting close with this simple one:

Please Login or Register  to view this content.

...But instead of being based on the "ActiveCell", I want it based on the button location which changes with each inserted row. Then i'll have to deal with clearing content as well.

Thoughts?

OK...found this that is simple and inserts a row above the button perfectly.

Please Login or Register  to view this content.

Now I just need it to copy down formulas from the row above the newly inserted row.

Hi TheBullND,

Please follow the Forum Rules by Wrapping any Code you may Post.

Return to your posts, Click on Edit>highlight the Code>Click on #, in the options immediately above in the window.

Thank you.

Hi TheBullND,

Thank you for for your prompt response regarding wrapping of Code.

It is always better to upload a sample Workbook. Now I fully understand your requirements.

I have also corrected your Dividing Formula so as not to return an error.

Please try the Workbook now.

Regards

Sorry TheBullND, but it is not doing what you say it does, on my side. I have tried it in different versions of Excel, without a problem.

Are we on the same "Page" here, or have you changed anything in the Code of my sample Workbook?

I am stumped. Please try this one in .xlsm format.

Hello TheBullND,

Please replace your Code with the Code below, and let me know if it now works for you.

Please Login or Register  to view this content.

Regards

Hello TheBullND,

I shall try to obtain a newer version of Excel 2007, and see how best we can solve your issue. Never had this problem before, it may have to do with something very simple.

Regards

Hello TheBullND,

Taking a long shot here, but please try the Code below;

Please Login or Register  to view this content.

Hello TheBullND,

Thank you for the feedback.

O.K. then, let's try it this way:

Please Login or Register  to view this content.

Hello TheBullND,


You are welcome.


I am glad that we could finally sort this one out.

If you are satisfied with the solution I had given you, then please mark your Thread as Solved.

You may also Click on the Star to the far left, at the bottom of this Post, to Add Reputation.

Thank you,

Regards