Rename a file before close

[daniels012]

Here is my code. It does everything I need except at the end I cannot figure out how to rename a workbook after I get data from it.

Sub EstimatingData()
    SavedLastProp = MsgBox("If you saved the last Proposal, press Yes, if you are not sure, press NO!", vbYesNo)
    If SavedLastProp = vbNo Then
        Exit Sub
    Else
    End If
    'ClearMerge
        Application.ScreenUpdating = False
    
    Dim wbSource As Workbook, wbDest As Workbook
    Dim wsSource As Worksheet, wsDest As Worksheet
    Dim wsSource2 As Worksheet, wsSource3 As Worksheet
    Dim fName As String
    
    ChDrive "C:\"
    ChDir "C:\Documents and Settings\Owner\My Documents\Dropbox\Clients\"
    'ChDir "C:\Clients\"
    
    fName = Application.GetOpenFilename
    On Error GoTo chas
    
    'if this workbook in same dir path, prevent reopen
    If fName = ThisWorkbook.Path & "\" & ThisWorkbook.Name Then
        MsgBox "You have chosen this workbook, choose another.", , "Already Open"
        Exit Sub
    End If
    
    Set wbDest = ThisWorkbook
    Set wsDest = wbDest.Worksheets("EstimatingData")
    Set wbSource = Application.Workbooks.Open(fName)
    Set wsSource = wbSource.Worksheets("Estimating")
    Set wsSource2 = wbSource.Worksheets("Foreman")
    Set wsSource3 = wbSource.Worksheets("Worksheet")
    
    'With wbSource    'Copies the Drawing and Picture tab
    '    .Sheets(Array("Drawing", "Pictures")).Copy _
    '    After:=wbDest(wbDest.Sheets.Count)
    'End If
    
    wsSource.Activate
    wsSource.Range("A1:K97").Copy
    wsDest.Range("a1").PasteSpecial xlPasteValues
    wsDest.Range("a11").Value = fName
    wsSource2.Range("B2:AA56").Copy
    wsDest.Range("n1").PasteSpecial xlPasteValues
    wsSource3.Range("B34").Copy
    wsDest.Range("I18").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Application.ScreenUpdating = True
    wbDest.Activate
    Range("a1").Select
    wbSource.Close (False)
    
chas:
End Sub

I need right at the end before closing the wbsource, to rename the file with the value in cell U17 Range("U17").value

Thank You for any help,
Michael

 

[GTO]

Hi there,

I did not run through the code you currently have, but you can make the changes wanted, .SaveCopyAs (which leaves an unloaded copy) and kill the wb you are in.

In a junk wb only until satisfied its what you want...

Sub exa()
    
    With ThisWorkbook
        .SaveCopyAs .Path & "\Copy_" & .Name
        .ChangeFileAccess xlReadOnly
        Kill .FullName
        .Close False
    End With
End Sub

Hope that helps,

Mark