I had trouble seeing what you were doing. Jim's code helped understand.

If I got it right...

You save the first version as book.xls, then book-1.xls, then book-2.xls just
looking for the next available number that isn't used. If that's correct, maybe
something like:

Option Explicit
Sub testme02()
Dim iCtr As Long
Dim testStr As String
Dim myFileName As String
Dim mySfx As String
Dim myFolder As String

myFolder = "P:\Folder\"

testStr = ""
On Error Resume Next
testStr = Dir(myFolder & "nul")
On Error GoTo 0
If testStr = "" Then
MsgBox "Design error--invalid drive/folder!"
Exit Sub
End If

myFileName = Worksheets("GEMFEDCCOrderForm").Range("F9").Value

iCtr = 0
Do
If iCtr = 0 Then
mySfx = ".xls"
Else
mySfx = "-" & iCtr & ".xls"
End If

If Dir(myFolder & myFileName & mySfx) = "" Then
'it ain't there, we can use that name!
Exit Do
Else
iCtr = iCtr + 1
End If
Loop

ActiveWorkbook.SaveAs Filename:=myFolder & myFileName & mySfx

MsgBox "File was saved as: " & myFolder & myFileName & mySfx

End Sub


Darrin Henshaw wrote:

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 ***

--

Dave Peterson