title: Merge specific sheets by adding the sheet name as the first column
body: <p>I have quite a number of sheets whose names end in <strong>_A</strong> or <strong>_B</strong>.
  <img src="https://i.stack.imgur.com/GbUPA.png" alt="enter image description here">
  <img src="https://i.stack.imgur.com/9z3na.png" alt="enter image description here">
  <img src="https://i.stack.imgur.com/VplHl.png" alt="enter image description here"></p>
  
  <p>I would like to merge all sheets ending in  <strong>_A</strong> or <strong>_B</strong> under one another. They have the same number of columns, but different number of rows. However, when merging, I want the sheetname to be repeated all the way down the last row of that sheet in the merged sheet. Result:
  <img src="https://i.stack.imgur.com/gcrkv.png" alt="enter image description here"></p>
  
  <p>I want the results to be saved in the same workbook but in a sheet called "merged".
  <br>
  <a href="https://drive.google.com/file/d/0B73smSNUQ01-OWxNOFFNWDBucjg/view?usp=sharing" rel="nofollow noreferrer">here is the example sheet if you would like to work on</a>.</p>
  
  <p><strong>what I have tried:</strong></p>
  
  <p><br></p>
  
  <pre><code>Sub Merge_into_One()
      Dim ws As Worksheet
      Dim TargetRow As Long
  
      Application.Calculation = xlCalculationManual
      Application.ScreenUpdating = False
  
      TargetRow = 1
      For Each ws In ActiveWorkbook.Sheets
          With ws
              If .Name Like "*" &amp; strSearch &amp; "_A" Or _
                .Name Like "*" &amp; strSearch &amp; "_B" Then
                  .Range("A1:C99").Copy
                  With Worksheets("Merged").Cells(TargetRow, 1)
                      .PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                      End With
                  TargetRow = TargetRow + 99
              End If
          End With
      Next ws
  
      Application.CutCopyMode = False
      Application.ScreenUpdating = True
  
  End Sub
  </code></pre>
  
  <p>But here, i am taking 99 rows, supposing that my rows will not exceed this number. However, I want to take exactly the same number of rows that appear in each sheet, not more not less. And here I cannot put the name of the sheet in the first column and repeat it until the last hit of the same sheet.</p>