Forum Discussion
Automatically inserting a row based upon a filter
I am hoping this will be a simple post for someone to answer, but I want an excel spreaadsheet to automatically add in a new row based upon a simple yes o no filter.
If Yes is selected, a new row needs to be added immediately underneath that row, but if no is selected then no additional row is added - the no part is easy because it can just be left as it is, it is the Yes answer I am struggling with.
I don't want the recipients of this to have to run a macro every time they get it so that where yes is selected a new row is added, i want them to have the spreadsheet and if they select yes one is automatically added.
If that is possible, is it possible for the new row to repeat some of the content of the line immediately above the new row (i.e. the original one where yes was selected) or will it have to create a blank row but copying only the filter box - just like would happen if I selected the row using the right mouse button and clicked insert?
Thank you in advance of your help as I just can't seem to locate an answer anywhere else.
4 Replies
It can be done automatically, but it requires VBA so users would have to allow macros when they open the workbook.
Could you explain in more specific detail what you want?
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
Thank you for your reply, sorry for the delays - the system reckoned I was posting too many lots messages (yet this is my only one!?) and so blocked me for hours.
I am hoping this works.
So the raw data appears like this:
I have labelled the 6th column with extra line needed.
If any of the cells in column 6 have "yes" selected instead of "no", it is those I want a new to appear below.
This is what I would like it to look like if yes were selected - I have made the text red so it is clear which have changed, but it is the row numbered 4 on the spreadsheet and also (now) the one numbered 6:
For those "Yes" ones a new line is now below.
On the new lines (what appears at the rows numbered 5 and 7), it would be ideal if certain items from rows 4 and 6 could appear - the code and date - but type, Detail 1, Detail 2, Extra line needed and Amount £ would be blank, whilst the Code + description cells would contain the same drop down list as used by all other cells in that column.
The link to onedrive is https://lucasrossx-my.sharepoint.com/:x:/g/personal/kevin_lucas_lucasross_co_uk/ET7wr_VlDn9Jo-t8Zhhzxq4BP35KC0hyuB26qOIzN46wxg?e=tuaFXd
See the attached workbook. It is now a macro-enabled workbook (extension .xlsm), so you'll have to allow macros when you open it.
If you want to inspect the VBA code, right-click the sheet tab and select 'View Code' from the context menu.
