title: Macro to loop through all sheets that are placed between two named sheets and copy their data to a consolidated file
body: <p>I have a template file which will be sent out to all subsidiaries of my company.</p>
  
  <ol>
  <li>The template has a tab named <em>start</em> and one named <em>end</em>.</li>
  <li>The subsidiaries will place a variable number of template submission sheets between these two names sheets and send them in to me to consolidate into one sheet in my consolidation file.</li>
  <li>I have written macros to copy each sheet into the consolidation file, but I currently need to run it sheet by sheet as I don't know how to loop.</li>
  <li>The copy macro sits in my Personal.xls file and the paste macro sits in the consolidation sheet.</li>
  <li>The loop macro would need to work between the <em>Source file</em> (could be any name) and the consolidation file which is called <em>Consolidation.xls</em>.</li>
  <li>Once all sheets are copied from a source file, I then open the next source file and start again, so macro would need to forget the old source file and remember the new one.</li>
  </ol>
  
  <p>This could literally save me hours each week if I could get a macro to work, so any help much appreciated.</p>