When I started learning VB about a year ago, I just jumped into it not
really taking the time to read up and learn on it, so my first programs
were shall we say, enough to get the job done. Now that I've taken the
time to learn what it is I was doing, I've realized I need to optimize
my programs.

One such program is this. The user has to input data into multiple
cells. When they are finished the workbook is saved with the contents of
one cell. The problem being is that it is possible in the run of a day
to have multiple files with the same name. My current way around this is
as so:

Dim MyFile, MyFile1, MyFile2, MyFile3, MyFile4, MyFile5
MyFile = Dir("P:\Folder\" & Worksheets
_("GEMFEDCCOrderForm").Range("F9").Value & ".xls")
MyFile1 = Dir("P:\Folder\" & Worksheets _
("GEMFEDCCOrderForm").Range("F9").Value & "-1.xls")
MyFile2 = Dir("P:\Folder\" & Worksheets
_("GEMFEDCCOrderForm").Range("F9").Value & "-2.xls")
MyFile3 = Dir("P:\Folder\" & Worksheets
_("GEMFEDCCOrderForm").Range("F9").Value & "-3.xls")
MyFile4 = Dir("P:\Folder\" & Worksheets
_("GEMFEDCCOrderForm").Range("F9").Value & "-4.xls")
MyFile5 = Dir("P:\Folder\" & Worksheets
_("GEMFEDCCOrderForm").Range("F9").Value & "-5.xls")

If MyFile4 = Worksheets("Sheet1").Range("F9").Value & _
"-4.xls" Then
ActiveWorkbook.SaveAs Filename:="P:\Folder\" & _
Worksheets("Sheet1").Range("F9").Value & "-5.xls"
ElseIf MyFile3 = Worksheets("Sheet1").Range("F9").Value _ & "-3.xls"
Then
ActiveWorkbook.SaveAs Filename:="P:\Folder\" & _
Worksheets("Sheet1").Range("F9").Value & "-4.xls"
ElseIf MyFile2 = Worksheets("Sheet1").Range("F9").Value _ & "-2.xls"
Then
ActiveWorkbook.SaveAs Filename:="P:\Folder\" & _
Worksheets("Sheet1").Range("F9").Value & "-3.xls"
ElseIf MyFile1 = Worksheets("Sheet1").Range("F9").Value _ & "-1.xls"
Then
ActiveWorkbook.SaveAs Filename:="P:\Folder\" & _
Worksheets("Sheet1").Range("F9").Value & "-2.xls"
ElseIf MyFile = Worksheets("Sheet1").Range("F9").Value _ & ".xls" Then
ActiveWorkbook.SaveAs Filename:="P:\Folder\" & _
Worksheets("Sheet1").Range("F9").Value & "-1.xls"
ElseIf MyFile = "" Then
ActiveWorkbook.SaveAs Filename:="P:\Folder\" & _
Worksheets("Sheet1").Range("F9").Value & ".xls"
End If

As you can see VB starts working down, and until it hits a file that
exists. If there is no file in the folder with a filename of cell F9,
then it saves the workbook as that value. And the rest of the ElseIf's
are ignored. If it does find a file that exists, then it works through
the rest of the ElseIf's and checks to see if a file exists names the
vaue of F9, and the "-1", etc.

As I said this method works, but it is definitely not the best way. I
figure I could do use a Loop to check through to see if the file exists,
but don't know how to start. Any ideas to point me in the right
direction? Thanks.

*** Sent via Developersdex http://www.developersdex.com ***