Can you modify one macro with another

[creyavaich]

The problem im having is this; I have a invoice created in excel that contains a dropdown box with a list of names(for salesperson). The user selects a name and enters other data (such as products selling) and then presses a button that exceutes a macro that copies the information to a different sheet.
One thing that does happen in this macro is that the total income is copied to a cell on a different worksheet and the cell that is copied to depends on the salesperson name (to track the total income of each salesperson) (done using a case statement to test the value of the selected salesperson name)

My problem is that i want to make a macro that allows the user to add a new salesperson to the dropdown list and add the new name as an extra case in the macro that copies information across sheets.(basically a macro that when run adds code to a specific area of another macro)

is this possible? If not its no problem

thanks for any advice

[raypayette]

It is possible to modify a macro with a macro, but it is very trick and dangerous. Have you tried using a dynamic range :

Pearson
Ozgrid
Contextures

[creyavaich]

Thanks, I found the websites useful for learning about dynamic ranges (i have not used them before). However after setting to refer the dropdown box to relate to the new define name the drop down box always displays the same number of options, this being 7 dispite the fact that there are only 6 options for salesperson name.(so there is a blank option at the bottom, if I add one more salesperson that one shows with no blank option and if I then add another that option dosnt show)

In the refers to for the define name I have

"=OFFSET(Salesperson!$A$2,0,0,MATCH("*",Salesperson!$A:$A,-1),1)"

Salesperson refers to the sheet name that consists of the list of salesperson names.

This problem is confusing as it appears that the reference of the defined name is absolute even with the code above. Any ideas to why this is?

Again, Thanks for any advice