Select Case using a Variable

[Cupragsw]

Hi this should be a simple thing but having one of those days

i and trying to get a selcte case to use a varible that can change

I have set the Varible to link to a cell on on one of the worksheets but the selcte case does not pick it up

i sm using

Please Login or Register  to view this content.

(note there are other cases but i cant give them a;ll out but once one works the rest can be changed)

Or would it be better to do something like this

Please Login or Register  to view this content.

i have assigned my varible like this

Please Login or Register  to view this content.

Not sure if thats right Sheet18 is alos called allocation

[Norie]

Can we see the rest of the code, in particulare the Select part of the Select case?

[Cupragsw]

Please Login or Register  to view this content.

[Norie]

It would really help if you posted all the code and/or uploaded a sample workbook.

[Cupragsw]

Please Login or Register  to view this content.

I need to it read from Sheet18 (allocation) Col L 1 onwards as these details may change from time to time otherwise it a case of changing the names in the case staement when we get new staff

also

how can i put this omewhere in my code so i can call it for each sheet i have 10 instead of coping it into each sheets code