Cannot find file to copy from when running macro

Question

Quick question which isn't a massive issue, just an annoying little thing.

When I run a certain macro, which vlookups within a different excel file, sometimes but not every time, I get prompted to select the file to copy from as shown in the screenshot.

The required file is the first in the list, it never gets renamed or moved:

I've also attached a screenshot of the macro code, it'd be great if someone could help me in having it so it runs without me having to select the file most times.

I know I don't have a full path to the file, I tried that but then it stopped worked so I'm not sure?

Range("A1").Select

Sheets.Add After:=ActiveSheet

Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(Sheet1!RC,2)=""91"",Sheet1!RC,"""")"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:A100")

Range("B1").Select
ActiveCell.FormulaR1C1 = _
    "=IF(VLOOKUP(MID(RC[-1],3,6),[StockQuantity.xlsm]Sheet7!C2:C10,9,FALSE)=0,"""",CONCATENATE(""89"",RIGHT(RC[-1],8)))"
Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B100")

Columns("B:B").Select
Selection.Copy
Range("C1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.CutCopyMode = False
Columns("A:B").Select
Selection.Delete Shift:=xlToLeft

picture of the code

I don't know why sometimes I don't have to select the file on occasion.

"I've also attached a screenshot of the macro code" -- please don't. Instead give a *self-contained* [mcve]. — John Coleman, Nov 05 '18 at 11:55
@wizhi Sorry guys, I've copied in the code now, just that relevant sections as it's quite a long macro, is that okay? — Lucas Wayne, Nov 05 '18 at 11:58
@JohnColeman Sorry guys, I've copied in the code now, just that relevant sections as it's quite a long macro, is that okay? — Lucas Wayne, Nov 05 '18 at 11:59
It is better, but really not okay. Your code is easier to *look at* now, but still quite impossible for anyone other than yourself to *run* (at least without an inordinate amount of work) and see what happens. Please read [mcve]. They are not easy to construct, but make a question much, much better. Furthermore, doing the work needed to create a minimal example often resolves the bug itself. The optimal Stack Overflow question is the one that is never asked because you figured it out in the process of writing a good question. — John Coleman, Nov 05 '18 at 12:02
do you normally have the file open when you run this code? I would say it should only work if the file is open and ask for path if it doesnt find any open excel with that name. — Pavel_V, Nov 05 '18 at 12:03
@Pavel_V The file is never open when I run the macro, yet sometimes o'm asked to copy it and other times not, might it be because the file was recently opened when I'm not required to copy it? — Lucas Wayne, Nov 05 '18 at 12:08
I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply that technique to your code to make it more reliable and faster. • I think the file `StockQuantity.xlsm` either needs to be open, or needs the full path in the formula. It might work without the full path if your current workbook is already saved in the *same* path as `StockQuantity.xlsm`. — Pᴇʜ, Nov 05 '18 at 12:11
do you have any other formulas that are using this file inside the file with macro? As far as I just checked as long as there is some fomula connected to the specified file when I open the file with the macro, then the macro works. When I open the file without any formula to that file and I run the macro, it asks for path. — Pavel_V, Nov 05 '18 at 12:16

score 1 · Answer 1 · edited Nov 05 '18 at 15:06

First stop selecting a cell before "doing something to it". Instead of saying "grab that cell!" and then "now do the following to what you've grabbed", you could simply say "do the following to that cell". Quicker and better :)

When you see:

Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(Sheet1!RC,2)=""91"",Sheet1!RC,"""")"

then normally you can strike out the SELECT <carriage return> ActiveCell. part and stitch the rest together. I hope I make some sense!

Re your code asking for a file sometimes but not always. In your code you specify a file that will be included in the cell formula, but you do not specify a path to the file so Excel will "hope" it's open, if not it will open up a prompt for you/the user to select a file in which to find that worksheet and cell reference.

So to resolve, you need to include the entire path to the proper file in your formula. Just do it manually first, then copy the resulting formula to your VBA code.

score 0 · Answer 2 · answered Nov 25 '18 at 22:44

Here is your code cleaned up:

Sub stuff()
Sheets.Add After:=ActiveSheet
Range("A1:A100").FormulaR1C1 = "=IF(LEFT(Sheet1!RC,2)=""91"",Sheet1!RC,"""")"
Range("B1:B100").FormulaR1C1 = "=IF(VLOOKUP(MID(RC[-1],3,6),[StockQuantity.xlsm]Sheet7!C2:C10,9,FALSE)=0,"""",CONCATENATE(""89"",RIGHT(RC[-1],8)))"
Columns("B:B").Copy
Range("B1").PasteSpecial Paste:=xlPasteValues
Columns(1).Delete Shift:=xlToLeft
End Sub

Put the full path before StockQuantity.xlsm to stop it prompting for the file.

Cannot find file to copy from when running macro

2 Answers2