VBA: Merge workbooks into one - Having trouble with Dir
<p>I'm wondering if it is possible if you could help me with an issue I'm having with my excel workbooks.</p>

<p>Lets say for example I have 2 workbooks in the same folder as the book with the running VBA code.</p>

<pre><code>main.xlsm
20160909- Beef v22_1.xlsx
20161015- Stew v21_0.xlsx
</code></pre>

<p>I want to open these books, and copy data from a cell range in a sheet - the key identifiers here being "beef" and "stew" since these wont change. This is very similar to what Pierre was attempting to do <a href="https://stackoverflow.com/questions/26059381/vba-merge-certain-worksheets-in-different-workbooks-into-one-workbook/26060729#26060729/" title="here">here</a>, but I don't need to go that far, I know the worksheet name and cell range I want to copy and I almost know how to copy them with </p>

<pre><code>Workbooks(beefWB).Sheets("Sheet2").Range("A1:E20").Copy _
    Destination:=Workbooks("main.xlsm").Sheets("combined").Range("E2")
</code></pre>

<p>The main issue I am having is the wildcard searching with Dir;</p>

<pre><code>Dim beefWB As String

beefWB = Dir(ThisWorkbook.Path &amp; "\*beef*.xlsx")
Do While beefWB &lt;&gt; ""
    Workbooks.Open Filename:=ActiveWorkbook.Path &amp; "\" &amp; beefWB
    beefWB = Dir
Loop
</code></pre>

<p>Excel can't seem to find the sheets. Since all the sheets start with "201" it will open them with "*201*.xlsx", it will even open the beef workbook with "*609*.xlsx" but it will not open with the actual keywords "beef" or "stew". I have attempted to switch tactic by using </p>

<pre><code>If InStr(beefWB, "beef") &gt; 0 Then
    Workbooks.Open Filename:=ActiveWorkbook.Path &amp; "\" &amp; beefWB
</code></pre>

<p>But I'm quite new to this so I'm probably messing that up one way or another.</p>

<p><strong>Bonus</strong>- If it were possible to open the most recent file of each type (beef and stew) that would be even better, but it is not nessessary.</p>

<p>Apologies since this seems to be a common question, but I hope you can help.</p>