Ronald,

What you recommend is pretty much what I had decided to do. I am
encountering one minor issue that I still need help with. The issue is I may
be grabbing a file and trying to save it at a location that doesn't exist...I
need to create some if statement that says if that happens then just move on
down the list of files. The line in code that is causing this issue is:

Workbooks(sCurrFName.SaveAs(Z.Value & sFileStamp & sCurrFName)

If this line of code causing an error, i.e. the path doesn't exist, then I
just want to close the open file, then have a message box pop and say "path
doesn't exist" and then goto NoFolder.

Here is the full code - please be gentle:

Sub FXFE_Controllable()

Dim sCurrFName As String
Dim sFileStamp As String
Dim Y As Range
Set Y = Range("b8")
Dim Z As Range
Set Z = Range("b12")
Dim sDest As String
Dim J As Range
Set J = Range("b13")



With Application
.ScreenUpdating = False
.DisplayAlerts = False
sCurrFName = Dir(Y.Value & "*.xls")
sFileStamp = Range("b5")

Do While sCurrFName < ""
Worksheets("Home").Range("b14") = sCurrFName

sDest = J.Value
If J.Value = 0 Then
MsgBox "No folder exists for " & sCurrFName, vbInformation
GoTo NoFolder
Else: GoTo NormalProcess
End If

NormalProcess:
Workbooks.Open (Y.Value & sCurrFName)
Workbooks(sCurrFName).SaveAs (Z.Value & sFileStamp & sCurrFName)
Workbooks(sFileStamp & sCurrFName).Close

NoFolder:
sCurrFName = Dir

Loop

.ScreenUpdating = True
.DisplayAlerts = True
End With

Range("b14").Clear

End Sub

"Ronald R. Dodge, Jr." wrote:

As for your array list that you need, one such option is to use a particular
column of an Excel worksheet to list the various characters/words/phrases
you are wanting to have VBA to compare against. You then use another column
to list the path where the file will be copied/moved to. By doing this, you
can then have VBA loop through the list and by the same token, don't have to
hard code it every time, should you want to modify the list and also have
the workbook as a shared workbook. Once the code finds a match, then the
code would pull the destination based on the same row the match took place
and the destination column. If you want to make it a bit more dynamic
rather than having a static range reference within VBA, you can also name
the 2 ranges, and then setup range objects based on those 2 range names.

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"hurlbut777" wrote in message
...
Ronald,

Thank you for your response. If I am reading your code correctly it
should
automate the excel file piece I need but I would have to build hundreds of
If
statements specificly listing out each folder...if possible I would like
to
avoid that but it is beginnning to look like I may just have to bite the
bullet.

"Ronald R. Dodge, Jr." wrote:

You can use something like the following code:

Public Sub pcdSaveFile()
Dim l_objFileSystemObject as Scripting.FileSystemObject,
l_objSourceFolder As Scripting.Folder
Dim l_objFiles as Scripting.Files, l_objCurrentFile as Scripting.File
Set l_objFileSystemObject = New Scripting.FileSystemObject
Set l_objSourceFolder =
l_objFileSystemObject.GetFolder("O:\ExcelFiles\Res ultsFolder")
Set l_objFiles = l_objSourceFolder.Files
For Each l_objCurrentFile in l_objFiles
If VBA.InStr(1, l_objCurrentFile.Name, "Red",vbTextCompare) 0
Then
l_objCurrentFile.Copy "O:\ExcelFiles\RedFolder\" &
l_objCurrentFile.Name, True
End If
Next
Set l_objFiles = Nothing
Set l_objSourceFolder = Nothing
Set l_objFileSystemObject = Nothing
end Sub

--
Thanks,

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
"hurlbut777" wrote in message
...
I have 5 folders (ABC_Blue, ABC_Green, ABC_Red, ABC_Yellow, Results).
The
folder named Results contains three files (Blue.xls, Green.xls,
Red.xls).

I need to create a macro to loop through the results folder, and copy
excel
files into the other 4 folders based on whether or not any part of the
folder
name contains the name of the exel files. As an example, since
ABC_Blue
folder contains Blue within its name, the Blue.xls file should be
copied
to
this location.

I would be one happy camper if the above could be accomplished, but I
wouldn't know what to do with myself if in addition some functionality
could
be added to show any files within the results folder that were not
copied
somewhere else. As an example, if there was an excel file name
Purple.xls
within the Results folder, it wouldn't be copied to another folder
because
there isn't one containing Purple within its name.

I know enough VBA to be dangerous, so if someone can help me get
started
I'm
sure I can muddle through the rest eventually.