From time to time I get a run error 1004:"method 'open' of object 'workbooks' failed and it takes me to the blue shaded code below:
Set thisWB = Workbooks.Open(filename:=sFileName).
When I restart my PC the error disappear but I wonder if someone can help me simplified this code.
Set thisWB = Workbooks.Open(filename:=sFileName).
When I restart my PC the error disappear but I wonder if someone can help me simplified this code.
Code:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.AskToUpdateLinks = False
Dim mydata As String
Dim sPath As String
Dim sFileName As String
Dim sSheetName As String
Dim pSheetName As String
Dim sRange As String
Dim res As String
Dim lastrow As Long, lastcolumn As Long
'Declare Arrays
Dim arrWB() As Variant
Dim thisWB As Workbook
'Clear contents in Control file
Sheets("Control").Range("b4:d120, g4:h120").ClearContents
'Constant Path
sPath = "C:\VBA\Automating\"
'open Data file
Workbooks.Open "C:\VBA\Automating\Data.xlsm"
'Message;close all workbooks
MsgBox "Please make sure all forecast models are closed and choose Cell C2 !!"
'In worksheet; forecast
sSheetName = "Forecast"
pSheetName = "Prior Forecast"
'message ; what column do you want to paste to ?
res = InputBox("What column do you wish to paste to")
'Array Models 1 thru 85
arrWB = Array("AFCU-automate.xlsx", "Alaska CU-automate.xlsx", "American Savings Bank-automate.xlsx",)
'When loop thru Array, build up the full file name string
For wbI = LBound(arrWB) To UBound(arrWB)
'Build the Filename
sFileName = sPath & arrWB(wbI)
'open Model wbI
[COLOR=#0000ff][B] Set thisWB = Workbooks.Open(filename:=sFileName)[/B][/COLOR]
'copy sum of invoice (SAP)
thisWB.Sheets(sSheetName).Range("A1983:A1983").Copy
thisWB.Sheets(sSheetName).Range(res & 1983).PasteSpecial xlPasteValues
'Copy all automated metrics
thisWB.Sheets(sSheetName).Range("A1999:b2044").Copy
thisWB.Sheets(sSheetName).Range(res & 1999).PasteSpecial xlPasteValues
'COPY RESULTS TO CONTROL (the rows.count, 7 for example is for the seventh column on the control sheet)
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 2).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range("I10").Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 3).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range(res & 1993).Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 4).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range(res & 1989).Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 7).End(xlUp)(2).Value = thisWB.Sheets(sSheetName).Range(res & 14).Offset(0, 1).Value
ThisWorkbook.Sheets("Control").Cells(Rows.Count, 8).End(xlUp)(2).Value = thisWB.Sheets(pSheetName).Range(res & 14).Offset(0, 1).Value
'Save and Close Model
Application.DisplayAlerts = False
thisWB.Save
thisWB.Close False
Application.DisplayAlerts = False
Next wbI
'Close the Data file without saving
Application.EnableEvents = False
Workbooks("Data.xlsm").Close savechanges:=False
MsgBox "Feed was completed!"
End Sub