Can't Close WorkBook after I Copy Information

[Ratriel]

Hi guys, Im new in the VBA

What I'm doing is opening a few files to copy the information of each of that to a main file, Im doing this after open a USERFORM with a button in the USERFORM

What I cant do is tell VBA to close the source files, it give a RUNTime Error 1004, Method Close of Object _WorkBook Failed.

If a run the code with a regular button it works, but I need to do with a button in the VBA USERFORM

here is my code:


Private Sub ExtractData_Click()


'Declarando Destino


Dim OrderTool As Workbook
Dim OrderSheet As Worksheet
Set OrderTool = Workbooks(ThisWorkbook.name)
Set OrderSheet = OrderTool.Worksheets("Price Data")


'Declarando Rutas Origen


Dim USpath As String
USpath = "Macintosh HD:Users:sergiocastillo:Dropbox:This Week:CaptainGeorge.USFood.xlsx"


Dim USpathLast As String
USpathLast = "Macintosh HD:Users:sergiocastillo:Dropbox:Last Week:CaptainGeorge.USFood.Last.xlsx"


'Declarando Origenes


Dim usFoodData As Workbook
Dim usFoodLastData As Workbook
'--------------------------------------------
Dim usSheetData As Worksheet
Dim usSheetLastData As Worksheet
'--------------------------------------------
Set usFoodData = Workbooks.Open(USpath)
Set usFoodLastData = Workbooks.Open(USpathLast)
'--------------------------------------------
Set usSheetData = usFoodData.Worksheets("USFood Price List")
Set usSheetLastData = usFoodLastData.Worksheets("UsFood Price List")


lrUS = usFoodData.Sheets("USFood Price List").cells(Rows.Count, 1).End(xlUp).Row
lrUSlast = usFoodLastData.Sheets("USFood Price List").cells(Rows.Count, 1).End(xlUp).Row


usSheetLastData.Range("A1:G" & lrUSlast).Copy Destination:=OrderSheet.Range("h1")
usSheetData.Range("A1:G" & lrUS).Copy Destination:=OrderSheet.Range("a1")


usFoodData.Close SaveChanges:=False
usFoodLastData.Close SaveChanges:=False

 

[RSpin]

You could try changing:

usFoodData.Close SaveChanges:=False
usFoodLastData.Close SaveChanges:=False

to:

Workbooks("usFoodData").Close SaveChanges:=False
Workbooks("usFoodLastData").Close SaveChanges:=False

Hope this helps.