Macro to change directory based on cell content

[Guest]

Hi guys,

I am new to Excel and have created a document that has a drop down list at
the top to change the header from 'Bank' to 'Savings'. I read in the
discussion group how to automatically save a file using a cell reference. I
must change directories, before saving and so far what I have (and it works)
is:

ChDir "G:\SAVINGS\Pending WRO"
ActiveWorkbook.SaveAs Filename:=Range("b8").Value

What I need to accomplish is in the active workbook, if Cell A1 = 'Savings',
then
ChDir "G:\SAVINGS\Pending WRO"
ActiveWorkbook.SaveAs Filename:=Range("b8").Value
But if Cell A1 = 'Bank' then
ChDir "G:\Bank\Pending WRO"
ActiveWorkbook.SaveAs Filename:=Range("b8").Value

Thanks for any assistance that can be provided.

 

[Rick Rothstein \(MVP - VB\)]

I am new to Excel and have created a document that has a drop down list at

the top to change the header from 'Bank' to 'Savings'. I read in the
discussion group how to automatically save a file using a cell reference.
I
must change directories, before saving and so far what I have (and it
works)
is:

ChDir "G:\SAVINGS\Pending WRO"
ActiveWorkbook.SaveAs Filename:=Range("b8").Value

What I need to accomplish is in the active workbook, if Cell A1 =
'Savings',
then
ChDir "G:\SAVINGS\Pending WRO"
ActiveWorkbook.SaveAs Filename:=Range("b8").Value
But if Cell A1 = 'Bank' then
ChDir "G:\Bank\Pending WRO"
ActiveWorkbook.SaveAs Filename:=Range("b8").Value

Maybe this line of code does what you want?

ChDir "G:\" & Range("A1").Value & "\Pending WRO"

Personally, I wouldn't bother with setting the ChDir at all; I would
eliminate the ChDir statement completely and just prepend the path onto the
value from B8...

ActiveWorkbook.SaveAs Filename:="G:\" & Range("A1").Value & _
"\Pending WRO\" & Range("B8").Value

Rick

 

[Guest]

I should have mentioned in my post the reasons I need to change the directory
is:
1 - multiple users of the form who will usually save the original document
to their own drive, and
2 - their could be a file with the same name and the only distinction is the
sub directory 'Bank' or 'Savings'

I will try what you have suggested. Thanks for your help, I will let you
know.

 

[Guest]

Hi Rick,

When I tried your first suggestion: ChDir "G:\" & Range("A1").Value &
"\Pending WRO", it saved the file to the current directory I was working in.
Your second suggestion works perfectly.

Thank you for all your help