Excel Leaving Data Source Workbook Open After Copy/Paste Data Macro is run

Question

I am very, very new to VBA. I am trying to automatically copy data through a macro from 8 dynamic "individual" workbooks to paste into one master workbook all layered on 8 tabs as those individual books will be continuously updated by various team members.

I have the macro below which runs fine but excel afterwards leaves the data source workbook open so that won't work with 8 individual files.

What code can I add to tell excel to close the data source file automatically after the micro is ran? Adding "Workbooks("C:\Users\MIRAR\Desktop\AD HOC\DEMO FILE - WIP\SHARNY.xlsx").Close" at the end of the code before End sub is not working and issuing error 9.

Code Below is to copy past data from 1 of the 8 workbooks on 1 tab.

Much appreciated!

Sub CopyingRange()
Workbooks.Open "C:\Users\MIRAR\Desktop\AD HOC\DEMO FILE - WIP\SHARNY.xlsx"

Dim CopyFromBook As Workbook
Dim CopyToWbk As Workbook
Dim ShToCopy As Worksheet

Set CopyFromBook = Workbooks("SHARNY.xlsx")
Set ShToCopy = CopyFromBook.Worksheets("Sheet1")
Set CopyToWbk = Workbooks("Consolidated Tracker File.xlsx")

Workbooks("C:\Users\MIRAR\Desktop\AD HOC\DEMO FILE - WIP\SHARNY.xlsx").Close 

End Sub

Excelosaurus Excelosaurus · Accepted Answer · 2017-11-08T23:09:45

Short answer is:

Workbooks("SHARNY.xlsx").Close

The better answer is to work with variables that directly point to workbooks:

Sub CopyingRange()
    Dim CopyFromBook As Workbook
    Dim CopyToWbk As Workbook
    Dim ShToCopy As Worksheet

    Set CopyFromBook = Workbooks.Open("C:\Users\MIRAR\Desktop\AD HOC\DEMO FILE - WIP\SHARNY.xlsx")
    Set ShToCopy = CopyFromBook.Worksheets("Sheet1")
    Set CopyToWbk = Workbooks("Consolidated Tracker File.xlsx")

    CopyFromBook.Close
End Sub

Excel Leaving Data Source Workbook Open After Copy/Paste Data Macro is run

2 Answers