title: How to use Vlookup inside VBA with a variable worksheet name
body: <p>I need to use a vlookup inside vba but the worksheetname constantly changes but can always be referenced as ActiveWorkbook.Worksheet(1) and ActiveWorkbook.Worksheet(2). So I used dim so all selection can refer to ws1 and ws2 but of course inside a vlookup formula that kind of script doesn't work. Hope anyone can help rewrite those vlookup formula lines.</p>
  
  <p>It regards the last 5 lines with the ActiveCell.FormulaR1C1 lines where i need a solution for 'ws2'! 
  Thanks for the help.</p>
  
  <pre><code>Dim ws1 As Worksheet
  Dim ws2 As Worksheet
  
  Set ws1 = ActiveWorkbook.Worksheets(1)
  Set ws2 = ActiveWorkbook.Worksheets(2)
  
  ws1.Select
  Selection.AutoFilter
  Range("G2").Select
  ActiveCell.FormulaR1C1 = "Web sales"
  Range("H2").Select
  ActiveCell.FormulaR1C1 = "Web stock"
  Range("I2").Select
  ActiveCell.FormulaR1C1 = "Total Sales"
  Range("J2").Select
  ActiveCell.FormulaR1C1 = "Total Stock"
  Range("F2:F71").Select
  Selection.Copy
  ActiveWindow.SmallScroll Down:=-102
  Range("G2:J71").Select
  ActiveWindow.SmallScroll Down:=-66
  Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
  Columns("H:H").EntireColumn.AutoFit
  Range("I5").Select
  Application.CutCopyMode = False
  Range("D3:D150").Select
  ws2.Select
  Range("D3:D150").Select
  ws1.Select
  ActiveWindow.SmallScroll Down:=-66
  Range("G3").Select
  ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-3],'ws2'!RC[-3]:R[42]C[-1],3,0)"
  ActiveWindow.SmallScroll Down:=-30
  ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-3],ws2!R3C4:R45C6,3,0)"
  </code></pre>