0

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

Luuklag
  • 3,897
  • 11
  • 38
  • 57
Richard
  • 91
  • 8
  • Have you looked for anything? Plenty of examples on this site. [Here is one](http://stackoverflow.com/a/25953980/4243498) – ChipsLetten Aug 03 '15 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 Aug 03 '15 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 Aug 03 '15 at 14:20

2 Answers2

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
MatthewD
  • 6,719
  • 5
  • 22
  • 41
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
user3476534
  • 220
  • 1
  • 4
  • 15