Insert rows with copying formula from above row

[masta-s]

Hi there!

This is going to be my first post and probably the first macros-enabled excel sheet created by me, so I hope for your help!

So I'm trying to do the following:

The sheet consists of 13 columns.
I want to protect it and then send to my colleagues. The main feature of it would be 2 buttons to insert the rows. The first button would insert all 13 columns below selected cell in a row and copy all formatting. Also cell g contains the formula which I'd like to be filled down (NOT COPIED, like I saw many times in the answers). So basically this is a recorded macro of what I'm trying to do:

Please Login or Register  to view this content.

The second button would do the following: it will insert the row below selected cell and merge cells from A to J with the cells from above rows (not all together, I need A72 to be merged with A 71, then B72 with B71, etc). Then two cells K and L would be just inserted and again cell M would be merged with above cell (like M2 with M1)
So below is the VBA code that I want to be done by just clicking the button:

Please Login or Register  to view this content.

L&G, if somebody would help me with that I would probably be one of the happiest men in the world.

I'm also attaching the sheet so you understand what I want exactly.
sample.xlsm
And to make me happy for the rest of my life - can you tell me how can I hide all columns starting from AA to the end and rows starting from 50?

[Winon]

Hello masta-s,

And to make me happy for the rest of my life

To make the Forum Moderators happy, and before you can expect a reply to your problem, you will have to read and follow the Forum Rules. > Wrapping your Code <, for instance.

[masta-s]

Hello masta-s,



To make the Forum Moderators happy, and before you can expect a reply to your problem, you will have to read and follow the Forum Rules. > Wrapping your Code <, for instance.

Winnon, thanks for help, hope this will work now :-)

[Winon]

Hi masta-s,

Merging Cells causes a myrid of problems. Would it be o.k. with you if we did not Merge any Cells?

[masta-s]

Hi masta-s,

Merging Cells causes a myrid of problems. Would it be o.k. with you if we did not Merge any Cells?

Winon,

Sure it will work. I just have an excel sheet where they've managed to apply that, but unfortunately it's password protected as well as VBA code is pw protected too. But it looks so nice...

Anyway, if there's a lot to do to make this work then sure at least pasting the rows without merging will do. Thanks in advance!

[HaHoBe]

Hi, masta-s,

maybe try these two modified versions of your macros:

Please Login or Register  to view this content.

I went from A to J on this, we could use an array to hold all Columns

Please Login or Register  to view this content.

Ciao,
Holger

[masta-s]

Hi Holger,

Thanks for your time spent to help me.
The first code seems to be working, but it shifts the selected row down instead of adding the one below. But it works!

As to the second one - that one just messes up the whole worksheet.

I've attached the updated sheet with proposed codes (I've assigned them to the buttons) so if you have time - could you please check it?

sample.xlsm

Thanks in advance!

[Winon]

Hi masta-s,

Try the attached WorkBook.

[HaHoBe]

Hi, Winon,

instead of unprotecting the worksheet prior to use of the macro and thereafter I tend to protect the Worksheet on Activation with the parameter UserInterfaceOnly:=True which allows macros to be effective on that sheet while normal actions canīt take place. Behind the Sheet:

Please Login or Register  to view this content.

@masta-s:
I strongly agree with Winon about Merged Cells - I should know how to handle them but try to avoid them whereever possible (my alternative is Center Across if needed or rely on Word for better "painting" abilities). Iīm sorry I messed the worksheet up - I didnīt test the code but that wasnīt at all expected to happen.

Ciao,
Holger

[masta-s]

Winon, thanks for time spent!

Unfortunately the first macro also shifts the rows down, and I don't see any formula copied... So if you could use the sample I've attached in my last message and check what can be improved?

[Winon]

@ HaHoBe,

I agree with the Password UserInterFace suggestion. I was just so caught up with everything else and the Merge Cells, that I have overlooked it.

However, I have changed your Merge code to Center Across Selection and it seems to be o.k., as long as the user remembers to select the Cell directly below the one to be "Stretched".

@ masta-s,

You can give this revised version a try as well.

[Winon]

Here you go then,

Also see the "Please consider" note at the bottom of this Post.