title: Excel VBA - Creating new sheet with categories and rows in between
body: <p>Objective: To have a Macro that has rows with content in one sheet separated by empty rows depending on the values of other sheets. Here is how the final output should look like:</p>
  
  <p><img src="https://i.stack.imgur.com/44K6T.jpg" alt="image from user"></p>
  
  <p>Situation now (manual): I go to Sheet 3, see which categories exist, and add them manually on Sheet 1 with red background. Afterwards, I go to Sheet 2, see how many sub-items exist per category, and add that many rows manually in Sheet 1.</p>
  
  <p>Picture of Sheet 2:</p>
  
  <p><img src="https://i.stack.imgur.com/39q7q.jpg" alt="picture of sheet 2"></p>
  
  <p>Picture of Sheet 3 (Categories)</p>
  
  <p><img src="https://i.stack.imgur.com/bloHI.png" alt="sheet 3"></p>
  
  <p>Situation with Macro: I run a macro which then creates the categories based on Sheet 3 and with empty rows between these categories based on the # of items in Sheet 2.</p>
  
  <p>Here's my code so far:</p>
  
  <pre><code>Sub AddingCategories()
  '
  ' AddingCategories Macro
  '
  
  ' here we copy the categories from the Categories sheet
      Sheets("Categories").Select                         'we select the sheet where the categories are
      Range("A1").Select                                  'we select the first cell with content
      Range(Selection, Selection.End(xlDown)).Select      'we can select all categories with content
      Application.CutCopyMode = False
      Selection.Copy                                      'we copy the content
      Sheets("Timeschedule2").Select                      'we go to the destination sheet
      Range("B11").Select                                 'We select the first row where we want content
      ActiveSheet.Paste
  
  'Here we format them to red
  
      Range("A11:B25").Select                             'since we just copied content, we need to have the cells with the formatting we want (in this case red)
      Application.CutCopyMode = False
      With Selection.Interior
          .PatternColorIndex = xlAutomatic
          .Color = 255
          .TintAndShade = 0
          .PatternTintAndShade = 0
      End With
      With Selection.Font
          .ColorIndex = xlAutomatic
          .TintAndShade = 0
      End With
  'Here we delete all rows without content
      Range("B11:B30").Select
      Selection.SpecialCells(xlCellTypeBlanks).Select
      ActiveWindow.SmallScroll Down:=-6
      Selection.EntireRow.Delete
  
  'Here we add the rows. Since we want to specify a number of rows to add, we first calculate this value using a countif function
  'this will tell us how many rows we need to add and we have it in Categories!C1. To add that many rows, we use a loop
  
  
  For i = 1 To Worksheets("Categories").Range("C1")
  
  Worksheets("Timeschedule2").Select
  Rows("12:12").Select
  Selection.Insert
  With Selection.Interior
          .Pattern = xlNone
          .TintAndShade = 0
          .PatternTintAndShade = 0
      End With
  
  Next i
  End Sub
  </code></pre>
  
  <p>With this code, I very proudly created the categories and managed to create the number of rows for the first category. 
  My problem is now that I don't know how to create the loop that will do this for all categories automatically (to do the first one, I specified to add them for row 12:12; but I have no way of knowing in advance where they should add the next new rows, nor how many times to loop since the number of categories will change from project to project) 
  I've been searching with Do Until IsEmpty, Do While, For, etc.. but I haven't been able to figure it out. </p>
  
  <p>How would you go about this? How can I improve my existing code?</p>
  
  <p>I am aware that my code is probably very inelegant, apologies for that!
  I'm primarily interested in learning how to program VBA more than making this particular macro work, so if you could explain to me as if I were a non-programmer 5 year-old I'd be thankful. </p>