assistance with VBA procedure being too long

[Sway2119]

so this is slightly complicated I have a worksheet named "Agents" range (A2:A21) you can enter names . I have a command button that has a macro that will create tabs based on the names entered into the range. Now I also have a worksheet "Overview" thats has the same Agent names range(B50:B69) from the Agent tab with a active X control combobox that has Jan - Dec. when you select from the drop down it should pull data from the each of the created tabs Example worksheet (Template) Range (L12:P23)

So based on the drop down (Teamrecbox) value in the worksheet "Overview" it needs to pull the corresponding data from each of the tabs that will be created - I wrote out the code the long way but now the procedure is too long.. any assistance I can get will be amazing

I've included a copy of my workbook

[mehmetcik]

Wow

This is gong to take a lot of time.

Please Login or Register  to view this content.

Can be replaced with

Please Login or Register  to view this content.

[Sway2119]

believe me it's been the BANE of my existence. I had it set up another way by agent but I was told that it needed to be a high overview of all agents at once.

[Sway2119]

here is a sample of the code I'm referring to, I have it written out for all possible tabs so it goes to Agent 20, then repeats based on the next value for the teamrecbox.

Please Login or Register  to view this content.

[MarvinP]

Hi Sway,

How about using loops to do the work above. Like:

Please Login or Register  to view this content.

I'm also a little worried about your code being behind a worksheet instead of in a module. The scope of code behind a worksheet should only be in that worksheet. If you want your code to deal with all those other sheets you should have it in a Module. See:
http://www.cpearson.com/excel/Scope.aspx

[mehmetcik]

Your DropDown404_Change macro can be reduced to:

Please Login or Register  to view this content.

Your teamrecbox.Change Macro can be reduced to:

Please Login or Register  to view this content.

Your AddButton_Click macro can be reduced to:

Please Login or Register  to view this content.

If a month is always selected and recselectbox is never empty then it might even be reduced to:

Please Login or Register  to view this content.