Insert New Rows to Table based on cell value

[adam2308]

On the attached example, I would like to click the command button and the code would insert the number of rows specified by cell BA2 to the next blank row (13 in this case). I then need the code to copy down the formulas and values of columns J:M in these 3 new rows in the table.Then the same in columns Q:S.

I have added a second worksheet (After) to show how I expect the first worksheet (Before) to look after running the code. Hope this helps to explain.

[antoka05]

You could use this macro:

Please Login or Register  to view this content.

Regards,
Antonio

[watersev]

hi adam2308, you could do that without VB at all, please check attachment

You data has been converted to a data table. In order to get values in L, R, S columns you need to input them as a formula. So instead 43% you need to input it =43% etc. Try to add entries manually or copy additional data below.

[adam2308]

Thanks Antonio, your code seems to work perfectly. I now need to add the second part to the code where I delete those rows marked as "Settled" after I have inserted my new rows.

I previously had this code to do that job

Please Login or Register  to view this content.

and I was hoping I could now piece the bits of code together but I'm having no joy. Can this be done?

Many thanks,
Adam.

[antoka05]

I'm sorry to answer so late but unfortunately I don't have setted notification for replies.

You could use this new code:

Please Login or Register  to view this content.

Regards,
Antonio

[adam2308]

Thanks Antonio. This is much closer but there was one small piece to fix. When the macro inserts the new rows then it needs to format these new rows the same as the the last one in the table. Currently all the columns are not formatted the same as above with validation drop-downs and date formats etc.

I may have mis-guided you a little on this when the example I posted in the original post had the next 3 rows (13-15) already formatted as above for some reason when in reality these rows will not be formatted. So, you may want to delete these rows before running the macro then you can see my problem.

Hopefully it's just a case of instructing the macro to 'format as above' when inserting the new rows?

Thanks again.