title: Convert Each Data set into 1 row
body: <p>Im trying to make a macro on Excel using VBA that will transpose each Data set into 1 row only.</p>
  <p><strong>Initial Format of Table</strong></p>
  <pre><code>No.               1
  Code              A1B1
  Date              10/25/21
  No.               2
  Code              A2B2
  Date              10/26/21
  </code></pre>
  <p><strong>Outcome</strong> should be like this in Sheet2</p>
  <pre><code>No.       Code          Date
  1         A1B2        10/25/21
  2         A2B2        10/26/21
  </code></pre>
  <p>I tried to copy Row that contain &quot;No.&quot;. But I want it to be extracted to cell B2. Where should I start?</p>
  <pre><code>    Sub CopyNo()
    Dim LastRow As Long
    LastRow = Worksheets(&quot;Sheet1&quot;).Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row
    With Worksheets(&quot;Sheet2&quot;).Range(&quot;A1:A&quot; &amp; LastRow)
      Worksheets(&quot;Sheet1&quot;).Range(&quot;A1:A&quot; &amp; LastRow).EntireRow.Copy .Range(&quot;A1&quot;)
      With .Offset
        .Replace &quot;No.&quot;, &quot;=No.&quot;, xlPart
        On Error Resume Next
        .Offset(1).SpecialCells(xlConstants).EntireRow.Delete
        On Error GoTo 0
        .Replace &quot;=No.&quot;, &quot;No.&quot;, xlPart
      End With
    End With
  End Sub
  </code></pre>