Open workbook prompts multiple times

Question

A few times a day I receive a file. I'm trying to automate it as much as possible and one part would include having the macro that lets you select a file to vlookup into (the file name is different every time). My macro runs, but for some reason it prompts you to select your file 3 times. I've tried a few variations on the code, but nothing worked. Does anyone have any insight as to why? It is prompting once when first opening the file, once when filling in the first cell with the formula, and again when the macro fills down column with the vlookup formula. I've pasted the relevant part below:

Dim MyFile As String
MyFile = Application.GetOpenFilename

Set firstWB = ActiveWorkbook
Set mySheet = ActiveSheet


Set wbLookup = Workbooks.Open(MyFile)

    firstWB.Activate
    mySheet.Range("T2").Select
    ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(RC[-18],'[wbLookup]tempemail'!R2C2:R123C20,19,0)"

    Range("S1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown

    Columns("t:t").EntireColumn.AutoFit

    Columns("T:T").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False


    wbLookup.Close False


    Range("U1").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("U1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Columns("u:u").EntireColumn.AutoFit

End Sub

Thanks!

What’s the name of your sub? – DisplayName Apr 27 '18 at 21:53 — DisplayName, Apr 27 '18 at 21:53

Tim Williams · Answer 1 · 2018-04-27T22:14:03.907

ActiveCell.FormulaR1C1 = _
   "=VLOOKUP(RC[-18],'[wbLookup]tempemail'!R2C2:R123C20,19,0)"

This will not work unless wbLookup is literally the name of your file. Excel sees this and prompts you for the actual name.

ActiveCell.FormulaR1C1 = _
   "=VLOOKUP(RC[-18],'[" & wbLookup.Name & "]tempemail'!R2C2:R123C20,19,0)"

might work better

This:

Columns("T:T").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

...could be replaced by this:

Columns("T:T").Value = Columns("T:T").Value

A lot of selecting/activating is unneeded and is better avoided: How to avoid using Select in Excel VBA

Open workbook prompts multiple times

1 Answers1