title: How to merge multiple workbooks into one based on workbooks names
body: <p>I am about preparing reports for manager. I have multiple excel files (always with one sheet) I would need to merge sheets to one workbook with multiple sheets (called the same as original workbook) based on name of original files. </p>
  
  <p>I need it to check the name of the file and based on the first four characters merge those files which have that characters the same. Then I want the new workbook to save with the name of those four characters.</p>
  
  <p>for example i have in one folder these files-></p>
  
  <pre><code>1111_AB_ABC
  
  1111_BC_AAA
  
  1222_CD_BBB
  
  1222_KL_XXX
  
  1222_HJ_OPD
  
  1666_HA_BNN
  </code></pre>
  
  <p>etc (there are around 300files like this, mostly there are 3 files with the same number at the beginning but there are few numbers for which I have four or five files). 
  Is there any possibility how to do this? </p>
  
  <p>I found some posts to merging workbooks to one masterfile, but nothing which is about merging files based on file name.</p>