CODEWITHSUNDEEP
excelvba
PokerMaster
PokerMaster

Reputation: 79

How do I close source file after copying?

I'm trying to close the original source file after copying it to my Dashboard. How do I do it?

Sub Load_Loan()
    Dim fNameAndPath As Variant  
    fNameAndPath = Application.GetOpenFilename(FileFilter:="*.XLS, *CSV", Title:="Select Tape")

    If fNameAndPath = False Then Exit Sub

    Workbooks.Open Filename:=fNameAndPath
    ActiveSheet.Copy After:=Workbooks(" Dashboard.xlsm").Sheets(Workbooks(" Dashboard.xlsm").Worksheets.Count)
    ActiveWorkbook.Close

    Workbooks(" Dashboard.xlsm").Activate

I had to debug

Upvotes: 0

Views: 529

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You need to be more precise in which workbook your worksheets are and avoid using ActiveSheet and ActiveWorkbook wherever possible.

Public Sub Load_Loan()
    Dim fNameAndPath As Variant  
    fNameAndPath = Application.GetOpenFilename(FileFilter:="*.XLS, *CSV", Title:="Select Tape")

    If fNameAndPath = False Then Exit Sub

    Dim WbSource As Workbook  'reference the opened workbook in a variable that we can use later to access/close it
    Set WbSource = Workbooks.Open(Filename:=fNameAndPath)

    Dim WbDestination As Workbook
    Set WbDestination = ThisWorkbook  'or if it is not the workbook this code is in then Workbooks("Dashboard.xlsm")

    'specify a worksheet in the source workbook
    WbSource.Worksheets(1).Copy After:=WbDestination WbDestination.Sheets(WbDestination.Sheets.Count)
    WbSource.Close SaveChanges:=False   'close and don't save
End Sub

Upvotes: 1

iDevlop
iDevlop

Reputation: 25262

it's safer if you use object variable: 

dim wb as workbook
set wb = Workbooks.Open(Filename:=fNameAndPath)
'do your stuff
wb.close

Upvotes: 1

Related Questions