Change Directory Based on Worksheet Name

I have a large number of worksheets in a workbook and a macro that will save each sheet individually to a folder of the worksheet name. The worksheet names range from AAXXX to AZXXX (where XXX is alphanumeric). In my I use the two following lines in the code to specify the directory and then the relevant folder within that directory to save the worksheets to, however I now need to save the worksheets to different subfolders based on the worksheet name.

Please Login or Register  to view this content.

So instread of having a load of folders inside C:\Client Jobs\Test\, named as per the worksheet name, I now have:

C:\Client Jobs\Test\North
C:\Client Jobs\Test\South
C:\Client Jobs\Test\East
C:\Client Jobs\Test\West

and folders inside each as follows:

North
AAXXX to AJXXX

South
AKXXX to AQXXX

East
ARXXX to ARXXX

West
ATXXX to AZXXX

How would I adapt the folder and file structure so that the relevant worksheets are saved in the correct folders, along the lines of

IF (wsName) = AAXXX to AJXXX then mDir = C:\Client Jobs\Test\North

ElseIf (wsName) = AKXXX to AQXXX then mDir = C:\Client Jobs\Test\South

and so on.

Many thanks

You could use code like this, based on the second letter of the name

Please Login or Register  to view this content.