excel VBA script VLOOKUP other file with unknown worksheet name

Question

Im trying to create a VBA script with a VLOOKUP to another workbook. The name of the 2nd workbook is always Stock.xls. The data in Stock.xls is always in the first worksheet, but the name of this sheet is different every day. It can be ARTLIST1, ARTLIST2, ARTLIST(3), ARTLIST-4 etc... but always starts with ARTLIST. How can I tell the VBA script just to use the first sheet, or the sheet always contains the word ARTLIST (e.g. ARTLIST*)

 ActiveCell.FormulaR1C1 = _
    "=VLOOKUP(C[-36],'[Stock.xls]ARTLIST1'!R1:R65536,13,0)"
Selection.Copy

Help is highly appreciated. Thanks in advance!

Richard

Have you looked for anything? Plenty of examples on this site. Here is one — ChipsLetten
– ChipsLetten, Commented Aug 3, 2015 at 12:59
Did you try searching? The INDIRECT() function does pretty much exactly this... You can use INDIRECT(A1) where A1 contains the sheet name you are looking for. — SpaceSteak
– SpaceSteak, Commented Aug 3, 2015 at 13:03
With INDIRECT i need to place the name of the worksheet in A1, correct? I need something full automatic.. where I do not need to fill in anything to select the first sheet. Ideally, instead of ARTLIST1 in my code above, it would state Worksheet(1) or ARTLIST*. — Richard
– Richard, Commented Aug 3, 2015 at 14:20

MatthewD · Accepted Answer · 2015-08-03 12:59:55Z

1

You can reference it by index and the get the name. Then you the name variable in the building of the formula.

Dim wsName as string
Dim ws As Excel.Worksheet

Set ws = Application.Worksheets(1)
wsName = ws.Name

ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-36],'[Stock.xls]" & wsName & "'!R1:R65536,13,0)"
Selection.Copy

answered Aug 3, 2015 at 12:59

MatthewD

6,8215 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user3476534 · Accepted Answer · 2015-08-03 13:31:35Z

0

Use the worksheet with index of 1 to get the first sheet:

For each ws in worksheetsFor Each ws In ActiveWorkbook.Worksheets
  If ws.Index = 1 Then
      'Do code, this is the first worksheet
  End If
Next ws

answered Aug 3, 2015 at 13:31

user3476534

2202 gold badges4 silver badges15 bronze badges

Collectives™ on Stack Overflow

excel VBA script VLOOKUP other file with unknown worksheet name

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related