title: create macro that will convert excel rows from single sheet to new sheets
body: <p>I need to create macro that will convert excel rows from single sheet to new sheets. </p>
  
  <p>I have 3 Rows of headers followed by lots of rows of data.  </p>
  
  <p>I would like to place each row on this sheet "Dept" into new sheets of their own (with the exception of the header rows).  On each new sheet created, I would like the top 3 rows (the headers) repeated and formatting copied (if possible), then the single corresponding row from the "Dept" sheet.  I would also like the new sheets to be named the value entered in column A (i.e. Ceiling Lights or Wall Lights from the example below).  </p>
  
  <p>I have no macro experience, so I'm having trouble taking code from previous answers and trying to apply it to my cause.  Thanks for the help!</p>
  
  <pre><code>       A           B           C          D
  </code></pre>
  
  <ol>
  <li><p>dept template // promos       // quicklinks // main banner</p></li>
  <li><p>where found  // content slot  // category  // attributes</p></li>
  <li><p>blank        // content asset // html     //  hero image</p></li>
  <li><p>Ceiling Lights // value  // value // value</p></li>
  <li><p>Wall Lights // value // value // value</p></li>
  <li><p>Floor Lights // value // value // value</p></li>
  </ol>
  
  <p>Converted to new sheets in the same workbook that have a single row after the 3 header rows:</p>
  
  <p>new sheet named: Ceiling Lights</p>
  
  <pre><code>       A           B           C          D
  </code></pre>
  
  <ol>
  <li><p>dept template // promos       // quicklinks // main banner</p></li>
  <li><p>where found  // content slot  // category  // attributes</p></li>
  <li><p>blank        // content asset // html     //  hero image</p></li>
  <li><p>Ceiling Lights // value  // value // value</p></li>
  </ol>
  
  <p>new sheet named: Wall Lights</p>
  
  <pre><code>       A           B           C          D
  </code></pre>
  
  <ol>
  <li><p>dept template // promos       // quicklinks // main banner</p></li>
  <li><p>where found  // content slot  // category  // attributes</p></li>
  <li><p>blank        // content asset // html     //  hero image</p></li>
  <li><p>Wall Lights // value  // value // value</p></li>
  </ol>
  
  <p>Here's the code I have so far...</p>
  
  <pre><code>Sub Addsheets()
  Dim cell As Range
  Dim b As String
  Dim e As String
  Dim s As Integer
  Sheets("Dept").Select
  a = "a4"
  e = Range(a).End(xlDown).Address 'get's address of the last used cell
   'loops through cells,creating new sheets and renaming them based on the cell value
  For Each cell In Range(a, e)
      s = Sheets.Count
      Sheets.Add After:=Sheets(s)
      Sheets(s + 1).Name = cell.Value
  Next cell
  
  Application.CutCopyMode = True
  
  Dim Counter As Long, i As Long
  
  Counter = Sheets.Count
  For i = 1 To Counter
      Sheets("Dept").Cells(1, 3).EntireRow.Copy
      Sheets(i).Cells(1, 3).PasteSpecial
  
  Next i
  
  Application.CutCopyMode = False
  End Sub
  </code></pre>
  
  <p>I can get the new sheets to create and name based on the cells in column A with the top portion of code, but when I tried adding code to have the first three rows (the header rows) copy to each of these newly created sheets I get Error 9 Subscript out of range for:         Sheets(i).Cells(1, 3).PasteSpecial.</p>
  
  <p>Not sure how to fix?
  Also, is there a way to preserve the header formatting (column widths)?</p>