It says " Runtime Error '1004' "
Here is the total coding I have thus far, all works okay until the last line:
Sub Update2()
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Msg = "This Macro Updates Information in Your File."
Msg = Msg & vbCrLf & vbCrLf & "Are you Ready to Continue?"
DialogStyle = vbYesNo + vbExclamation + vbDefaultButton2
Title = "File Update"
Response = MsgBox(Msg, DialogStyle, Title)
If Response = vbYes Then
Else
End
End If
'Sets the MacroFrance file as MacroFile to be later activated throughout the macro
Set MacroFile = ActiveWorkbook
Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date
'set path for files - change for your folder
Const myDir As String = "H:\Trackers\2012\VS\"
'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)
'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
If objFile.DateLastModified > dteFile Then
dteFile = objFile.DateLastModified
strFilename = objFile.Name
End If
Next objFile
Workbooks.Open myDir & "\" & strFilename
Set FileSys = Nothing
Set myFolder = Nothing
'set latest file to use formula
Set wbVS = Workbooks.Open(myDir & "\" & strFilename)
strFormula = "=INDEX('[" & wbVS.Name & "]VS UK'!$C$27:$C$39,MATCH(VLOOKUP(VLOOKUP(D12&E12,'2013'!$G$3:$J$119,4,FALSE),'" & wbVS.Name & "]VS UK'!$C$27:$C$39,1),'" & wbVS.Name & "]VS UK'!$C$27:$C$39,0)+1)"
Workbooks("2013 Shareshift_Yield Preferences.xlsm").Worksheets("UK").Range("G12").Formula = strFormula
Yes the workbook is open
Thanks again
Bookmarks