File Location Based on Cell Reference
Hi Monk,
In a standard module, try something like:
'===========
Public Sub aTester()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim arrNames As Variant
Dim arrFolders As Variant
Dim Res As Variant
Dim sStr As String
Const sName As String = "MyFile" '<<=== CHANGE
Const sElseFolder As String = _
"C:\Users\Other\" '<<=== CHANGE
arrFolders = VBA.Array("C:\Users\Paul\", _
"C:\Users\Mark\", _
"C:\Users\Joe") '<<=== CHANGE
arrNames = VBA.Array("Paul", _
"Mark", _
"Joe") '<<=== CHANGE
Set WB = ActiveWorkbook '<<=== CHANGE
With WB
Set SH = .Sheets("heet1") '<<=== CHANGE
Set Rng = Sh.Range("A1") '<<=== CHANGE
Res = Application.Match(Rng.Value, arrNames, 0)
If IsError(Res) Then
sStr = sElseFolder
Else
sStr = arrFolders(Res - 1)
End If
.SaveAs Filename:=sStr & .Name & ".xls", _
FileFormat:=xlWorkbookNormal
End With
End Sub
'<<===========
Note that the constant sElseFolder value is
provided to allow for the case in which an
unrecognised (or empty) value is enterd in the
cell of interest.
As an alternative, you might consider replacing
the arrays with a Select Case construct - see
VBA help for details, or post back.
---
Regards.
Norman
"Monk" wrote in message
...
I have a worksheet cell (say a1) with values such as Paul, David, Mark etc.
What I am struggling with is to develop a macro which will use the data in
that cell to determine the location the file will be saved to when the
macro
is run.
i.e If a1= Paul, the file location will be F:/Paul's
Directory/Filename.xls
If a1 = Mark, the file location will be F:/Mark's Directory/Filename.xls
Any suggestions would be appreciated.
|