Code to: Open another workbook and close current

[daniels012]

I want code to:
Open EstimatingSheet.xls
SaveAs current workbook as Range B13 &".xls"
Open EstimatingSheet.xls workbook from somewhere on C:\
Then close the one with B13.xls


If I need to explain further, I will

Michael

 

[jindon]

Ok
How about?

Sub SaveToSidonna()
    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
    strPath = "\\Sidonna\c\Estimating\"
    fname = Dir(strPath & 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
    End If
    ThisWorkbook.SaveCopyAs strPath & myFileName & ".xls"
End Sub

 

[daniels012]

I had to add a parenthesis here:
fname = Dir(strPath & Range("B11") )& "*.xls"
And here:
myVal = Val(Replace(Replace(fName, ".xls",""),Range("b11").Value,"") )

I get an error on this line of code??

fName = Dir()

Michael

 

[daniels012]

I get a
Runtime Error 5
Invalid procedure call or argument

 

[daniels012]

ideas?

 

[Bubo]

I do not know wether this helps, but I had problems to change to network drive. This Dir function was not work with network drive like \\.

I learn the solution from Jindom:

Dim myDir As String
myDir = "\\xxx\yyy" '<- change here
CreateObject("WScript.Shell").CurrentDirectory = myDir
MsgBox CurDir

 

[jindon]

I had to add a parenthesis here:
fname = Dir(strPath & Range("B11") )& "*.xls"
And here:
myVal = Val(Replace(Replace(fName, ".xls",""),Range("b11").Value,"") )

I get an error on this line of code??

fName = Dir()

Michael

The first one should read as

fname = Dir(strPath & Range("B11") & "*.xls")

 

[daniels012]

Wow
That really seems to be working.
Jindon,
I appreciate all your help on this. I am sorry I did not explain very well in my earlier descriptions.

We will be testing this on all our different computers. I'll stay in touch.

Thank You, ray:
Michael