trying to add the new name of a file in a cell

How do I add the new numbered file name to the "Estimating" worksheet in cell G68?

Here is the code:

Code:

---

Dim wb As Workbook, myFileName As String, myVal As Long, myMax As Long
    Dim strPath As String, fname As String, i As Integer
    If MsgBox("Have you printed your worksheets yet?", vbYesNo) = vbNo Then Exit Sub
    Application.ScreenUpdating = False
    Sheets("ESTIMATING").Select
    ActiveWorkbook.Save
    strPath2 = "\\5.111.222.646\Estimating\"
    fname = Dir(strPath2 & Range("B11") & "*.xls")
    If fname = "" Then
        myFileName = Range("b11").Value & "1.xls"
    Else
        Do While fname <> ""
              myVal = Val(Replace(Replace(fname, ".xls", ""), Range("b11").Value, ""))
              myMax = WorksheetFunction.Max(myMax, myVal)
              fname = Dir()
        Loop
        myFileName = Range("b11").Value & myMax + 1 & ".xls"
    End If
        If Dir(strPath2, vbDirectory) <> "" Then
           
            ThisWorkbook.SaveCopyAs strPath2 & myFileName
        Else
            MsgBox "The directory to save the file cannot be found.  So your data will not be sent."
            Exit Sub
        End If
End Sub

---

would this work?
Maybe after line:
If Dir(strPath2, vbDirectory) <> "" Then
add:
ThisWorkbook.ActiveSheet.Range("G68") = myFileName

You should explicitly name the sheet

[vba]

ThisWorkbook.Worksheets("the sheet name").Range("G68") = myFileName
[/vba]

xld,
thank you that seems to work!

May I also ask, about some error trapping?
I need to have a message box come up that makes sense.
For example:
If Error 52 comes up because the directory strPath2 = "\\5.111.222.646\Estimating\" is not found then
msgbox "make sure your network connection is turned on then try again"
End Sub

How/where can I enter code?
Thank you again,
Michael

Not tested, but you could try this

[vba]

Dim wb As Workbook, myFileName As String, myVal As Long, myMax As Long
Dim strPath2 As String, fname As String, i As Integer

If MsgBox("Have you printed your worksheets yet?", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
'Sheets("ESTIMATING").Select
'ActiveWorkbook.Save
On Error Resume Next
strPath2 = Dir("\\5.111.222.646\Estimating\", vbDirectory)
On Error GoTo 0
If strPath2 = "" Then


MsgBox "make sure your network connection is turned on then try again"
Exit Sub
End If

fname = Dir(strPath2 & Range("B11") & "*.xls")
If fname = "" Then
myFileName = Range("b11").Value & "1.xls"
Else
Do While fname <> ""
myVal = Val(Replace(Replace(fname, ".xls", ""), Range("b11").Value, ""))
myMax = WorksheetFunction.Max(myMax, myVal)
fname = Dir()
Loop
myFileName = Range("b11").Value & myMax + 1 & ".xls"
End If
ThisWorkbook.SaveCopyAs strPath2 & Application.PathSeparator & myFileName
[/vba]

For what ever reason I get the error 52 on this line:
fname = Dir(strPath2 & Range("B11") & "*.xls")

Thank You
Michael

Try this

[vba]

fname = Dir(strPath2 & Application.PathSeparator & Range("B11") & "*.xls")
[/vba]

Nope,
Still get Error 52

Nope,
Still get Error 52