VBA to insert multiple new lines in table with specific functions

Hello,


Been using the forum for a while to find all my VBA related query solutions and been pretty successful so far. However, I am in a process of working on an increasingly complex template and with my limited knowledge of excel and VBA - need some of the Guru's help.

I have attached a sample file that, in its essence, is the same as the template I am working on.

Data is produced by a system and copied - everything from column A to column L and columns O, P, Q and R are manual and has some formulas in.

What I want is a button on the sheet that, when clicked, adds a new line in each of the tasks (above each "Total" in column C) - ideally I'd want the new lines to also have formulas that other lines in columns P, Q & R have.

To make it even more annoying, in an ideal world I'd like the newly added lines to give an option of selecting the employee from the same list that the vlookup is referring to in the Rates table column. (e.g. 3 new lines are added in the existing table providing an option of some sort to select, which employee to add (the same for all three lines)).

I hope that made sense.

Onto Problem number 2 - I have tried to look, but can't seem to find a solution that works for me. I need a quick an easy way to Add up the totals within the table for each task. e.g O19 should =sum(O14:O18) and R19=sum(R14:R18). Usually once I paste the system data, there are hundreds of lines so I am trying to find a good way to automate these calculations (perhaps another button that can be used once the inside data has been manipulated as needed and ready to total up?)


I hope this makes sense but definitely let me know, if I can clarify some things up and huge thanks in advance, I really appreciate any help and time anyone is willing to spend replying [Blocked Image: https://www.excelforum.com/images/smilies/smile.gif].

Demo file for Macro help.xlsx

Hi Roy, thanks for your reply.

The problem is that the auto report, which is partly copied into the said table to build the rest of it has to include those subtotals.

Hence, I always knew that conventional excel functions won't be able to understand what I want them to do.

My idea was to somehow make excel understand what I want a sum in the last table column if the column C is "Total" and to lock in the sum range - somehow identify the cells based on column A and/or B values not being empty?

Unfortunately, there is nothing I can change about the formal of the data export and it is quite crucial I don't change it because loads of teams use it in the current format so I'm looking for a clever way to basically work around the excel rule breaking

Everything in Columns A to L are just copied so the totals for each Task are there and realistically people need to see those totals, so I am just trying to figure out a way to tailor Columns O to R to mimic the same format in terms of information shown.

Hi royUK,

Any chance you could help out with the part for a Button that would insert new,blank table rows above every "Total" in column C? And in the ideal world text like "New Employee" would be inserted in those added rows' C column and become "selected" in order to be able to type a new name once and all inserted rows would duplicate the same name.

Hope ^ makes sense

Thanks a lot in advance

I did and I really liked the double click function but I ran it past some of the managers who will need to use the template the most and all of them really want to have a button that inserts all the rows above the "Total" cells in the C column o was just wondered if there is a way to do that (and highlighting the cell etc as per my previous reply)?

I really appreciate your help and input of this so thank you

Hey,

yeah they really want a button that would insert multiple rows (one above each "Total")

Everyone argues that they would use all the rows

Do you not think one button could do the trick?

But as always - very welcome to any ideas and demos you can provide

Hi Roy,

Thanks so much for this, I think that, in terms of row adding functions, is exactly what the team is after. Is there any chance/way to also have sort of option box that would allow the user to choose people from the list on the other tab of people to add in newly added rows' C column?

In ideal world the list would be searchable in the box(?) that pops up when the rows are added but unsure if that is possible.

Appreciate your help a lot, thanks a lot

Hi Roy,

Yeah apologies for that. Think they have a very specific format in mind in terms of usability and they are pushing for it.

Thanks a lot for your help and input, I greatly appreciate it

That is true, but you know how difficult it can be with stuff like this when people have a specific stuff in their minds....

I do think it is the last thing they really want so hopefully almost there