append several sheets to a 'Results' sheet

[dazz]

Hi everyone!

Im new to this.. I have several sheets in a workbook, and want to copy Range A1 to N20 in all the sheets to a 'Results' Sheet, by appending them one after the other in the summary sheet. I have the following macro but it is over-writing, and not appending. How do I fix this? Will appreciate any assistance :


Sub z()
Dim ws As Worksheet
Dim wsDest As Worksheet
Set wsDest = Sheets("Results")
For Each ws In ActiveWorkbook.Sheets
If ws.Name <> wsDest.Name Then
ws.Range("A1", "N20").Copy
wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Next ws
End Sub

[mancubus]

hi & welcome to vbax.

it works for me.

[Aussiebear]

Try this, for I'm assuming you might have a typo in the code you submitted,

Sub z()
Dim ws As Worksheet
Dim wsDest As Worksheet
Set wsDest = Sheets("Results")
For Each ws In ActiveWorkbook.Sheets
If ws.Name <> wsDest.Name Then
ws.Range("A1:N20").Copy
wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial xlPasteValues
End If
Next ws
End Sub

[snb]

Sub M_snb()
For Each sh In Sheets
If sh.Name <> "Results" Then sheets("Results").cells(rows.count,1).end(xlup).offset(1).resize(20,14)=sh.Range("A1:N20").Value
Next
End Sub

[dazz]

Sub M_snb()
For Each sh In Sheets
If sh.Name <> "Results" Then sheets("Results").cells(rows.count,1).end(xlup).offset(1).resize(20,14)=sh.Range("A1:N20").Value
Next
End Sub

Thank you SNB, this works!!!!! :):):):):)