copy a range from multiple worksheets to one worksheet

[fedds2]

I have a macro which inputs QC data as a text file then creates separate worksheets for each QC. It then calculates mean, SD and CV for the different analytes. The number of analytes can vary. This coding although crude works well.
I now want to create a summary sheet in order to print out the data. Each sheet has a Range J1:M12 with my required data.
How do I copy this range allowing for spacing etc onto one worksheet?

kind regards,
Peter

 

[pboltonchina]

Try this on a copy of your workbook

Sub Generate_List()
Dim ws As Worksheet, cell As Range
Application.ScreenUpdating = False
If SheetExists("List") Then
    Sheets("List").Select
    Cells.ClearContents
Else
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "List"
End If
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "List" Then
        ws.Select
        Range J1:M12.Copy Destination:=Sheets("List").Cells(Rows.Count, "A").End(xlUp)
            End If
       Next ws
Sheets("List").Select
Application.ScreenUpdating = True
End Sub
 
Private Function SheetExists(SheetName As String) As Boolean
Dim x As Worksheet
On Error Resume Next
Set x = ActiveWorkbook.Sheets(SheetName)
SheetExists = (Err = 0)
Set x = Nothing
End Function