[vba] Grab Numerous Columns & Compile Into One

[DataBlake]

I'm at a loss for how to solve this one.
I need to grab part numbers from column A on specific named sheets within the workbook. Lets say they are sheets named "1" "2" and "3".
When i run the macro i need it to grab everything in column A from these sheets and put them compiled into the A column of sheet "4"

any help would be greatly appreciated.

 

[mohadin]

try this

Sub collet_A()
    Dim Vout As Variant, ct As Long
    Dim SheetNames As Variant
    ReDim Vout(1 To Sheets.Count - 1)
    ReDim Z(1 To Sheets.Count - 1)
   SheetNames = Array("MTH", "WP", "MKK", "TTW", "W1", "RHH")
    For Each sheetname In SheetNames
        lenx = Sheets(sheetname).Cells(Rows.Count, "A").End(xlUp).Row
        len_t = len_t + lenx
        a = Sheets(sheetname).Range("A1:A" & lenx).Value
        ct = ct + 1
        Vout(ct) = Array(a)
        Z(ct) = Application.Transpose(Application.Index(Vout(ct)(0), 0, 0))
    Next
    For i = 1 To UBound(Z)
        Sheets("sheet4").Range("a1:a" & UBound(Z(i))).Offset(x) = Application.Transpose(Z(i))
        x = x + UBound(Z(i))
    Next
End Sub