Duplicate second last row of a table

[MagnusNovak]

Hi,

I have a table that contains a certain number of lines. Each line contains either input from users or formulas. The last line of my table is just the footer (displaying copyright info). I'm looking for a way to add a line at the bottom of the table (just above the footer) which would still include the formulas (i.e. not a blank line) but none of the user's input of previous lines. The idea is to be able to increase the size of the table by adding a new clean (but not empty, -> formulas still there) line every time the macro is run.

Attached is an exemple of what I'm looking for.

Also, I don't know if there is an easy way to duplicate a line with cleaning user data but keeping formulas so I was thinking that an alternative solution would be that the last line of the table (before the footer) is hidden (so the user never uses it to input anything) and is used as template when duplicating / adding new lines.

Thanks a lot,
Magnus

[Pauleyb]

Well, you're not really using tables...
Highlight cells A4:E6 and then hit Ctrl-T (to create a table). Tell it that you table has headers. Now, go to cell E6 and hit Tab. This will generate a new row for your table, copying any defined formulae. It should look just like your result.

[MagnusNovak]

Thanks Pauleyb. Sorry I should have mentioned, I'm looking to do that with VBA.

[sintek]

One way...

Please Login or Register  to view this content.

[MagnusNovak]

Thanks Sintek, much appreciated. The formula works great with the example but I'm afraid it would become overly complicated for a table that contains multiple complex formulas in different columns (which is the case in my situation, but not the simplified example). Hence the idea of having the last row of the table being used as a template for duplication by the macro (the last row would be hidden so user doesn't input data and formulas are already all here). By duplicating this hidden line, then I would assume we don't have to even bother with formulas in the VBA code itself.

Thanks

[sintek]

The formula works great with the example

Only giving you what you asked for...
If this is actually not what you require then upload a sample depicting your actual file setup...and explain in detail what you require...
Anyway, hiding a row for formula template use is not the ideal solution...Have a look at UserForm Interface for data capture...

[Pauleyb]

I am curious why you wish to duplicate in VBA what Excel already does with tables?

Paul

[MagnusNovak]

Well, I'm trying to have a button on the spreadsheet that allows a user to easily add a row to a table (while keeping formulas) in a locked sheet. I thought there was no alternative to VBA to do such thing, correct? But maybe I'm missing an excel capability that could easily be associated with a button / macro (not an expert with that).

sintek: not sure to follow your point regarding UserForm Interface? I'm not trying to capture any data from users, simply add line to a table (or more precisely duplicate the last line in order to keep formatting and formulas in place). The tricky part is that if the last line was already used / filed by the user, I dont want to copy the whole content, hence the idea of have a clean line hidden that could be duplicated and used as a template for additional lines anytime the user runs the macro.

Thanks a lot for the help
Best

[sintek]

Still not sure what you are after...See post 6 requirement...

[Pauleyb]

Okay, so the sheet is locked and you want the button to add the row and thus need VBA. I'd still recommend using a Table. Then to add the row, you would do something like this:

Please Login or Register  to view this content.

I'll let you determine the proper locked/unlocked state and method.

[MagnusNovak]

Thank you Pauleyb, it works perfectly. I'm actually surprised that this piece of code is able to add a row while keeping existing formulas intact (I would have thought that it would only add a clean / empty line)

Thanks again