Merge ranges from specific sheets by adding the sheet name as the first column

[tabrizbalasi]

I have quite a number of sheets whose names end in _A or _B.

I would like to merge all sheets ending in _A or _B under one another., only ranges A:C until last value in column A. (There is always a value in cells in column A, but not necessarily in corresponding columns B and C, that is why I take count of rows based on A.)
They have the same number of columns, but different number of rows across these sheets. However, when merging, I want the sheetname to be repeated all the way down the last row of that range from each sheet in the merged sheet. Result:

I have tried this but I run into error in line:

ws.RangeToMerge.Copy Destination:=Worksheets("Merged").Range("B" & RowsPresent + 1)

Here is the full code:

Sub Merge_Resposes()


Dim RowsToMerge As Integer
Dim RowsPresent
Dim RangeToMerge As Range

For i = 1 To ActiveWorkbook.Worksheets.Count
      If InStr(Worksheets(i).Name, "_A") <> 0 Or InStr(Worksheets(i).Name, "_B") <> 0 Then
            Set ws = Worksheets(i)
            RowsToMerge = ws.Cells(Rows.Count, "A").End(xlUp).Row
            RowsPresent = Sheets("Merged").Cells(Rows.Count, "A").End(xlUp).Row
            Set RangeToMerge = ws.Range("A1:C" & RowsToMerge)
            ws.RangeToMerge.Copy Destination:=Worksheets("Merged").Range("B" & RowsPresent + 1)
            Worksheets("Merged").Range("A" & RowsPresent + 1) = ws.Name

      End If
Next

End Sub

Could somebody help me debug this and run a working version?
Thanks.

 

[needhelp2]

No worries, use this addin . its free and can do more if you need. RDBMerge, Excel Merge Add-in for Excel for Windows