I have this macro which copies the information from one book to another through the vlookup function, the macro works only when I paste the values from one book to another I open a window to select the destination book for update the values of the same, I would like that from the time you open the destination book those values are updated without having to ask me again and again as this function is for several sheets and asks me to update those values every time I will copy them in a new one page, as a result of not updating them shows me # N / A the opposite of if I update them which shows the value I'm looking for in the function.
Public Sub VLOOKUP()
Dim i As Integer
Dim e As Integer
Dim c As Integer
Dim FileName As Variant
Dim scheduleBook As Workbook
FileName = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx")
If FileName = False Then
MsgBox "No File Selected", , "Error"
Else
Set scheduleBook = Workbooks.Open(FileName)
i = 2
e = 1
c = 8
Line1:
If e < c Then
Workbooks(1).Activate
Sheets(e).Activate
Sheets(e).Range("E2").Select
ActiveSheet.Range("E2").FormulaR1C1 = "=VLOOKUP(R[0]C[-2],'[scheduleBook]Sheet1'!C1:C8," & i & ",false)"
ActiveCell.Offset(0, -2).Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 2).Select
Range(ActiveCell, ActiveCell.End(xlUp)).Select
Selection.FillDown
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("E2").Select
If e < 7 Then
i = i + 1
e = e + 1
ActiveSheet.Next.Select
GoTo Line1
Else
End If
Else
End If
End If
End Sub
If you could tell me one way that this is not shown and that the values are automatically updated would be great, thank you very much in advance