tabrizbalasi
New Member
- Joined
- Sep 14, 2014
- Messages
- 2
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:
Here is the full code:
Could somebody help me debug this and run a working version?
Thanks.



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:
Code:
ws.RangeToMerge.Copy Destination:=Worksheets("Merged").Range("B" & RowsPresent + 1)
Here is the full code:
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.