title: Consolidating worksheets into one
body: <p>I have a few excel worksheets in the style of</p>
  <h2>Organization 1</h2>
  <hr />
  <h3>Name Occupation</h3>
  <p>Chris - contracter</p>
  <p>Jane - employee</p>
  <hr />
  <h2>Organization 2</h2>
  <hr />
  <h3>Name Occupation</h3>
  <p>Bob - Facilities</p>
  <p>Trey - Software Developer</p>
  <hr />
  <p>And would like these worksheets to be combined into a master worksheet with the format</p>
  <h3>Organization Name Occupation</h3>
  <p>1 - Chris - contracter</p>
  <p>1 - Jane - employee</p>
  <p>2 - Bob - Facilities</p>
  <p>2 - Trey - Software Developer</p>
  <p>Using VBA I'm able to pull all the data into the one worksheet using</p>
  <pre><code>NumSheets = Worksheets.Count
  
  Worksheets(1).Select
  Sheets.Add
  ActiveSheet.Name = &quot;All&quot;
  For X = 1 To NumSheets
  Worksheets(X + 1).Select
  curAgency = ActiveSheet.Name
  NumRows = ActiveSheet.UsedRange.Rows.Count
  Rows(&quot;5:&quot; &amp; NumRows).Select
  Selection.Copy
  Worksheets(&quot;All&quot;).Select
  ActiveSheet.Range(&quot;B1&quot;).Paste
  Selection.End(xlDown).Select
  ActiveCell.Offset(1, 0).Select
  Worksheets(X + 1).Select
  Range(&quot;A1&quot;).Select
  Next X
  Worksheets(&quot;All&quot;).Select
  Range(&quot;A1&quot;).Select
  End Sub
  </code></pre>
  <p>But I am unsure how to insert the column and append the organization name to each group on the master list.</p>