File saving

[kris01]

Hi,

I've written a simple macro to import a coupe of particular ranges from various different files, but every time it imports the range from a specific sheet, it saves prior to closing the file.

I've tried a few things to stop the auto save upon closing but to no avail. I also want the screen to stop flickering whilst the code is running. What am i doing wrong. Any help on improving the speed also would be appreciated.

Thanks

Also, I wish to remove the 'windows' part of the code, because there's a much better to way to write it. Being a simpleton here.

 

[rlv01]

On the Save issue.

Sub NoSave()
    Dim WB As Workbook

    'Open Workbook
    Set WB = Workbooks.Open(Filename:="C:\Users\MyUser\Documents\AAAB\Test21.xlsm")

    'Close workbook without saving
    WB.Close SaveChanges:=False
End Sub

FWIW, when you post an image of your code instead of something that can be copied and pasted, it is difficult for others to experiment with it. Which means your chances of getting help drop significantly. Instead, use the free XL2BB tool to post your data in a way that makes it accessible to others.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[kris01]

Hello.

I did try the WB.close Save changes:=false line before and it didn't work.

This is my code, but I still getting the pop box "Do you want to save the changes you made" How do I turn this off?

Sub Import_Test()

Dim WB As Workbook
Dim WBBM As Workbook

Set WB = Workbooks.Open(Filename:="Q:\Finance\Accounts\Finance\Revenue Reporting\Contracts\2024 Contracts\5104 - XXX\XXXX Park Summary Report.xlsm")

Sheets("Control").Select
Range("NPREC").Copy

Windows("Client Money Rec v6.xlsm").Activate
Sheets("Client Report Recs").Select
Range("B2").PasteSpecial xlPasteValues

WB.Close Savechanges:=False
Application.Calculation = xlCalculationManual


Set WBBM = Workbooks.Open(Filename:="Q:\Finance\Accounts\Finance\Revenue Reporting\Contracts\2024 Contracts\5001 - XXXXX\XXXXXX.xlsm")

Sheets("Control").Select
Range("BRIGHTREC").Copy

Windows("Client Money Rec v6.xlsm").Activate
Sheets("Client Report Recs").Select
Range("H2").PasteSpecial xlPasteValues

WBBM.Close Savechanges:=False

Windows("Client Money Rec v6.xlsm").Activate
Sheets("Rec").Select
MsgBox "Month End Recs have been imported"
Sheets("Rec").Calculate


End Sub

 

[rlv01]

You did not mention the popup box previously. Usually you use this statement: Application.DisplayAlerts = False

Not tested.

Sub Import_Test()
    Dim WB As Workbook
    Dim WBBM As Workbook
    Dim WBClient As Workbook
    Dim ImportFile1 As String, ImportFile2 As String, DestinationWorkbook As String
    
    ImportFile1 = "Q:\Finance\Accounts\Finance\Revenue Reporting\Contracts\2024 Contracts\5104 - XXX\XXXX Park Summary Report.xlsm"
    ImportFile2 = "Q:\Finance\Accounts\Finance\Revenue Reporting\Contracts\2024 Contracts\5001 - XXXXX\XXXXXX.xlsm"
    DestinationWorkbook = "Client Money Rec v6.xlsm"
    
    On Error Resume Next
    Set WB = Workbooks.Open(Filename:=ImportFile1)
    Set WBBM = Workbooks.Open(Filename:=ImportFile2)
    Set WBClient = Workbooks(DestinationWorkbook)
    On Error GoTo 0
    
    If WB Is Nothing Then
        MsgBox "Error opening workbook." & vbCr & vbCr & ImportFile1, vbOKOnly Or vbCritical, Application.Name
        Exit Sub
    End If

    If WBBM Is Nothing Then
        MsgBox "Error opening workbook." & vbCr & vbCr & ImportFile2, vbOKOnly Or vbCritical, Application.Name
        WB.Close False
        Exit Sub
    End If
    
    If WBClient Is Nothing Then
        MsgBox "Cannot find workbook." & vbCr & vbCr & DestinationWorkbook, vbOKOnly Or vbCritical, Application.Name
        WB.Close False
        WBBM.Close False
        Exit Sub
    End If
    
    WB.Sheets("Control").Range("NPREC").Copy
    WBClient.Sheets("Client Report Recs").Range("B2").PasteSpecial xlPasteValues
    Application.DisplayAlerts = False
    WB.Close Savechanges:=False
    Application.DisplayAlerts = True
    
    Application.Calculation = xlCalculationManual
    
    WBBM.Sheets("Control").Range("BRIGHTREC").Copy
    WBClient.Sheets("Client Report Recs").Range("H2").PasteSpecial xlPasteValues
    Application.DisplayAlerts = False
    WBBM.Close Savechanges:=False
    Application.DisplayAlerts = True
    
    WBClient.Activate
    WBClient.Sheets("Rec").Activate
    MsgBox "Month End Recs have been imported"
    WBClient.Sheets("Rec").Calculate
End Sub

 

[kris01]

That's perfect. Thank you.