excel - Insert Column A from all sheets into column A of a new sheet

Question

I want to stack all of the values from all sheets' column A into a new sheets' Column A. The columns are of different lengths depending on the sheet. I was successful with inserting the columns previously but it inserted the formulas not the values of the columns and it inserted them horizontally not vertically.

Sub Vlookpage()
Dim WS As Worksheet
Dim i As Integer

'add sheet and name it lookup
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Lookup"
'take all column "A"s and add them to "vlookup"
    For i = 1 To Worksheets.Count - 1
    Worksheets(i).Range("A:A").Copy
    Worksheets(i).Range("A:A").PasteSpecial Paste:=xlPasteValues
    Worksheets(i).Range("A:A").Copy
    Worksheets("Lookup").Range("A:A").Insert (xlShiftDown)
        Next i
'purge duplicates
    Worksheets("Lookup").Range("A:A").RemoveDuplicates
End Sub

The columns are still being inserted horizontally rather than stacking

Answer 1

Please, try the next code:

Sub Vlookpage()
 Dim WS As Worksheet, shLkp As Worksheet, i As Integer
 Dim lastRowL As Long, lastRowWs As Long, arrAA

   'add sheet and name it lookup
    Set shLkp = Sheets.Add(After:=Sheets(Sheets.count))
    shLkp.Name = "lookup"

   'take all column "A"s and add them to "vlookup"
    For Each WS In Worksheets
        If WS.Name <> shLkp.Name Then
            lastRowWs = WS.Range("A" & rows.count).End(xlUp).row
            lastRowL = shLkp.Range("A" & rows.count).End(xlUp).row
            arrAA = WS.Range("A1:A" & lastRowWs).Value

            shLkp.Range("A" & lastRowL + 1).Resize(UBound(arrAA), 1).Value = arrAA
        End If
    Next WS
   'purge duplicates
    shLkp.Range("A:A").RemoveDuplicates
End Sub