Copying formulas, formatting etc automatically

[Kurt Smart]

Hi

I have a hidden row at the top of my sheet that has all the formatting, formulas, data validation etc applied, currently when I creating new rows (and not able to drag down due to end of data) I copy from this hidden row and paste in the new row create after the last line of data (and then drag down).

Is there a way using VBA to automate this so everytime a new row of data is added excel copies from my hidden row and pastes to the next empty to ensure I no longer have to keep dragging or copying and pasting?

And i'm avoiding using tables as its a shared workbook

Thanks in advance

 

[vletm]

Kurt Smart
How would You 'tell' to Excel, that You'll add a new row?
and
with a sample file, it would be possible to test Your settings.

 

[Kurt Smart]

Hi vletm,
I thought it maybe easier for VBA to search for the last empty row with no data in and add all the formatting etc to this last empty row.

So the sheet starts blank, apart from the headers (row 2) and the hidden row (row 1). So Id need the code to copy formatting, formulas, data validation from row 1 to row 3 which is currently blank. Now when the user adds information into row 3 then row 4 gets filled in.

Ill sort a sample file out once I have a few spare mintutes

 

[vletm]

Kurt Smart
... and even try to find few more minutes to answer to my question ... How ...?

 

[Marc L]

Hi !

I have a hidden row at the top of my sheet that has all the formatting, formulas, data validation etc

As any hidden row is not necessary just using an Excel table
'cause that's yet automatic when adding a new row to an Excel table …
​