Macro to sort, copy and paste to another sheet

[tool_fairy]

Hi y'all,

I’m hoping someone will be able to help me.

I'm assuming I need a macro for this so obviously let me know if there's another way.

I've attached two files. One with the expected result and one without. I'll try and be as clear as I can. The relevant data for the macro is in the 'Data' sheet. There is the staff member name in column A and the team code in column C. The team code then relates to the table starting in A1 which gives the managers name in column B next to the team code. The other sheets in the workbook are split in to teams named as per the manager's name.

The managers name may change and the teams that different staff are in may also change and there also be varying number of staff. The data is pasted from other workbooks and there are duplicates of the name.

I need the macro to copy and paste the names of the different staff members into cell A5 downwards on the relevant teams. It would also need to remove duplicate names. So for example, Name 01 at the top is in team 3, referencing the table at the top in A1:B6 shows that team 3 manager is Craig so this then needs to copied to the ‘Craig’.

Does all this make sense? You’ll see I’ve already populated the formula’s for columns B:D in the separate team sheets SUM based on names in column A, it just needs them to populate.

I hope this all makes sense and isn’t too difficult to do. Please let me know if you need me to clarify anything.

Thank you in advance.

[tfurnivall]

Well, there are a couple of questions! First, though, thanks for stating at least a rudimentary business case for what you're trying to accomplish. I don't know if you've ever done any database development, but there is a concept known as normalization which would repay some study. Just as an example of what can cause confusion, you have a nice little table of Managers and Team Names in cells A1 through B6, but then you have a repeating set of data of a completely different structure in cells A8 through G51. This data unfortunately has no column headers, so we are forced to make what sense we can of it! If you were to move the set of managers to a different worksheet, then we would have less possibility of confusion. Adding those headings would then allow us to figure out what column B in the lower data is all about. and why column D is blank, and what the meaning is of columns E through G.

You see what I'm getting at. Yes - it's tedious, and yes it seems to be unnecessary, but remember you've been beating your head against the brick-wall of this problem for some time, and what seems obvious to you is far less so to us!

Given the small number (6 in your example) of managers, I'd add a sheet of Managers, and possibly a list of each separate input spreadsheet (the data that you simply copied to get values in there), and then a separate worksheet for them. In this way you can work not only with the data you need, but also enough metadata to make your intentions and methods clear.

Your basic formulae are accurate - where the macro will help is in managing the movement and processing of the data which you will be accumulating. Once you decouple the name of the manager from the name of the team, it doesn't matter who is managing - the results will flow properly to the appropriate team - as they seem to do in the sheets you have named after the managersw (rather than the team name, which might have made it all clearer)..

Hope this helps, good luck with your application!

Tony

[stnkynts]

Try this

Please Login or Register  to view this content.

[tool_fairy]

Thanks for both for your replies.

stnkynts, that macro works brilliantly, the only problem is that it doesn't remove duplicates. Is it possible to update this cause other than that is spot on.

tfurnivall, I admit that the data sheet is very poorly laid out but that is just cause it isn't actually for reference for the end user. Essentially, that is weekly information and the workbook I posted is for the monthly figures. The weekly books are on individual books which are named after the week commencing date. For this reason I couldn't set them up to automatically reference to the monthly sheet. The weekly data doesn't need to be on the monthly sheet, only the totals, but the easiest way I could think of to calculate the monthly data was to paste each weeks data on to single sheet, 'data' in this instance then the team names sheets would referene against that. I completely understand that this isn't at all efficient and it's possible to write some nice macro's to do this all for me much simpler but this seems a much larger task to ask for assistance with than what I came up with.

In regard to the team code and the manager name, the team code is more relevant to the weekly sheet but the teams are actually known by the managers name as opposed to the code which was created by me for the purposes of another sheet.

The blank columnand the columns not referenced in any of the formula aren't relevant for this sheet it's, only on the weekly but as this info is pasted it's still there. Again, figured this was easiest for someone to paste all of the info rather than just certain columns.

I understand what you're saying about the different sheets but part of the problem is the fact that within a given month the staff in the team may change, this combined with the fact that the data doesn't even really need to be in the sheet in the first place so wanted it to take up as little space as possible.

Saying all this, i've been having to do quite a bit of piecemeal learning on excel recently, trying to work out how to do things with what I can piece together, so any advice is always greatly appreciated.

[xladept]

Hi Tool,

I also wrote a routine for you - but it requires a Jane & Claire sheet

Please Login or Register  to view this content.

[tool_fairy]

Excellent. That does the job. Oddity is that we don't actually need the monthly info for Jane & Claire but can just delete that sheet afterwards, no problem.

Only slight thing, if this isn't too much hassle, is it possible to reference the team code rather than the manager name. For example, if the person is in team 1, cell A1, then paste the sheet name of whatever is in b2. In my sheet it is Mel, and the macro pastes to the sheet called Mel but if the manager changed, say it became James, then it would need to paste to the sheet called James. So if the manager changed the user would only need to update the cell rather than the macro. Does that make sense?

[xladept]

Hi Tool,

The -1 in red will eliminate the need for the Jane & Claire sheet and now the sheet name is gleaned from column B on the data sheet:

Please Login or Register  to view this content.

BTW - I believe!

[tool_fairy]

Thanks so much. That did it

[xladept]

You're welcome and thanks for the rep!