How to copy ranges from several sheets to one sheet

Question

I want to copy ranges from several worksheets to one sheet.

The range to copy is C3 to the last row of data.
I need to paste it into a column on the main sheet in B6, then repeat the process on the next sheet (from C3 again) into the next column C6 and so on to column J.

I tried:

Set WkSh = ActiveSheet
Set DatShs = Sheets(Array("E0303_0", "E0304", "E0305", "E0306", "E0307", "E0308", "E0309", "E0310", "E0311_0"))
Set DatSh = Sheets(DatSh)  'I get Run time Error '13' Type mismatch here
Set Lrow = DatSh.Cells(Rows.Count, "C").End(xlUp)
TnD = DatSh.Range("C:B").Find("*", , , , xlByRows, xlPrevious).Row
Set RngGrp = DatSh.Range("TnD", Lrow)

Sheets("E0303_0").Range(RngGrp).Copy
ActiveWorkbook.WkSh.Range("A6").Paste

ActiveWorkbook.Sheets("E0304").Range("C3" & Lrow).Copy
ActiveWorkbook.WkSh.Range("C6").Paste

ActiveWorkbook.Sheets("E0305").Range("C3" & Lrow).Copy
ActiveWorkbook.WkSh.Range("D6").Paste

ActiveWorkbook.Sheets("E0306").Range("C3" & Lrow).Copy
ActiveWorkbook.WkSh.Range("E6").Paste

ActiveWorkbook.Sheets("E0307").Range("C3" & Lrow).Copy
ActiveWorkbook.WkSh.Range("F6").Paste

ActiveWorkbook.Sheets("E0308").Range("C3" & Lrow).Copy
ActiveWorkbook.WkSh.Range("G6").Paste

ActiveWorkbook.Sheets("E0309").Range("C3" & Lrow).Copy
ActiveWorkbook.WkSh.Range("H6").Paste

ActiveWorkbook.Sheets("E0310").Range("C3" & Lrow).Copy
ActiveWorkbook.WkSh.Range("I6").Paste

ActiveWorkbook.Sheets("E0311_0").Range("C3" & Lrow).Copy
ActiveWorkbook.WkSh.Range("J6").Paste

Answer 1

Your code makes no sense after the first 2 lines. You are trying to set a sheet to itself Set DatSh. What you want to do is loop through the array. Your lastrow is not a row number, but a range and you are trying to add to a cell. The following is the logic you want to use, you can modify as needed.

Sub test()

    Dim SheetArray As Variant
    
    Set SheetArray = Sheets(Array("E0303_0", "E0304", "E0305", "E0306", "E0307", "E0308", "E0309", "E0310", "E0311_0"))
    
    For i = 1 To SheetArray.Count
        LR = Sheets(i).Cells(Rows.Count, 3).End(xlUp).Row
        Sheets(i).Range(Sheets(i).Cells(3, 3), Sheets(i).Cells(LR, 3)).Copy
        ActiveSheet.Cells(i, 6).Paste
    Next i
    
    
End Sub