Insert a new row at the bottom of named Excel range using VBA and copy formular

[Jfejsa]

I need to add/insert a new row at the end of a named range (defined name) to expend the named range and copy formula from the row (4 cells) above to inserted row using Excel VBA.

There are three named ranges on the spreadsheet, 11 spreadsheets in the workbook and I would have insert new rows as needed by the user. I was thinking of creating 3 buttons or a drop down list on each spreadsheet but I am certain there are better methods to use. I am not very familiar with Excel VBA so I am confused.

I googled the question everywhere but can't find the answer.

Could anyone help please.

Thank you.

 

[NARAYANK991]

Hi ,

Can you upload your workbook ?

Narayan

 

[Jfejsa]

As you'll see there are 11 spread sheets in the workbook, plus three I used for testing (JohnTest, JohnTest2, and FoodList). Each spread sheet will have 3 define name lists/tables, and some way of adding a new row to each named list by either a button or a drop down list. Each list will have 5 columns (Food Item, Food Group, Food Type, Weight (gm/ml) per 1 child,
and Weight (gm/ml) for number of children at service).

All data will be either selected using drop down lists, automatically copied from another workbook/spread sheet depending on Food Item Selection or calculated using a sum function so I would like to copy all controls (combo boxes) and formula in each cell into new row when it's created. As well I would like all formatting to be copied across too.

At this stage only JohnTest2 has 3 defined lists (MorningTea, Lunch3, AfternoonTea) but all spread sheets will have named lists.

I would like to have an easy way using a button or dropdown list to add a new row to a specific named list located on a chosen spread sheet.

I really appreciate you having a look.

Thank you Naryan.

 

[Nebu]

Hi:

If I understood you correctly, does simply converting the range into a table will give you what you are looking for? Or am I missing out something.

Thanks
Nebu

 

[NARAYANK991]

Hi ,

To add to what Nebu has posted , can you detail all the steps that will be done by the user ?

If we assume that a button is made available in the Ribbon , so that it is available always , irrespective of which worksheet is active , is the user expected to merely click the button ?

Should all the actions take place on the active worksheet ?

I assume that what you want done should happen only on the worksheets named John Test , John Test2 and FoodLists ; is this correct ?

If this is not correct , then the ranges in the worksheets named Day 1 , Day 2 ,... have data just beneath the ranges ; can this data be move elsewhere so that the rows under the ranges are all blank , and available for overwriting ? This way , it will be easier to define dynamic named ranges for the data , so that as and when a new row of data is added , it is automatically included in the definition of the named range.

Narayan

 

[Jfejsa]

Hi Nebu,

For some reason, I didn't even think about using 3 tables on each spreadsheet.

My manager wanted a button on each day spreadsheet (day 1 to day 11) to insert a new row for specified meal time (breakfast, lunch, or afternoon tea); however creating 3 tables without headers on each sheet should work if I can figure out how to hide table headers. Using a tab key should be easy for him to add a new row and copy all formatting and formulas across; however, my manager doesn't want to have column headers to prevent admin from changing the sort order of entered meals and to make it look nice (no headers for 3 tables, headers shown in uploaded file).

Hi Narayan,

All the action should take on the active sheet.

Worksheets named John Test and John Test2 are my play worksheets and will not be included on in the final workbook.

Worksheet called FoodLists will be a hidden worksheet used as a reference spreadsheet to populate drop-down list that will allow the user to select the Food Item (meal name), such as kidney beans on selected day spreadsheets.

Once the user selects Food Item, the code/formula will then somehow have to populate Food Group, Food Type and average Weigh for selected Food Item and automatically calculate the Total Weight for number of children in the class (number of children ($C$1 * Weight - ie,. E18, E19, E20 etc).

If we look at a John Test 2 spreadsheet, for example, if the user selects Orange from the Food Items drop-down list (don't have a drop-down list yet), the program should use the selected Food Item (orange) to find the corresponding name in the Food List list on the hidden FoodLists spreadsheet, automatically add the Food Group (Fruit), Food Type (Fresh, Canned, Frozen) and Weight (22) from the FoodLists reference list to newly created or modified row. The last column on added/modified row (F6, for example...) should automatically calculate 484 if there are 22 children in the class).

Hope that makes sense.

PS: Refer to embedded image in JohnTest2 spreadsheet for more info.

Thank you both for your assistance.

John

 

[Nebu]

Hi:

Here are the tables without headers.

Thanks

 

[Jfejsa]

Thank you very much.

That looks great but how did you delete table headers. When I selected table headers and selected Delete Table Columns, the process deleted all content in every column in the selected table, not just headers.

PS: I do want to keep description headers, such as:

• Morning Tea: Assorted fruit, veg sticks and bread
• Lunch: Chilli con comes with rice
• Afternoon Tea: Fruit platter + veg sticks, dip, sultanas and crackers

and so on...

 

[Nebu]

Hi:

Go to table design view and un-check the header row to remove the headers from the table.

Thanks

 

[Jfejsa]

Thank you Nebu.

Have a lovely day.

John