Trouble iterating over both worksheets and columns in VBA Excel

Question

I currently have data in a certain column in 78 worksheets in the same worksheet that I would like to copy into another sheet in my Workbook titled "Sheet 2". Essentially I am taking the numbers in the Range B3:B195 in each of the 78 worksheets and then pasting it into a column in "Sheet 2" so that when the sub is finished, Sheet 2 should have 78 columns each with the data from one of the worksheets. However, when I run the macro nothing happens in the worksheet and when I step into the macro it seems as if the loops are just skipped.

Sub TransferData()
Dim numSheets As Long
Dim columnsAcross As Long
Dim lengthOfColumn As Long
Dim columnCounter As Long
Dim sht As Worksheet
Dim y As String

For numSheets = 2 To numSheets = 79
    columnCounter = 1
        For lengthOfColumn = 1 To lengthOfColumn = 192
            y = "B" & (columnCounter + 3)
            Worksheets("Sheet 2").Range(Cells(lengthOfColumn, numSheets), Cells(lengthOfColumn, numSheets)) = Worksheets(numSheets).Range(y)
            columnCounter = columnCounter + 1
        Next lengthOfColumn
Next numSheets

End Sub

Answer 1

Sub TransferData()
Dim numSheets As Long
Dim columnCounter As Long
Dim wb As Workbook

    Set wb = ThisWorkbook
    columnCounter = 1
    For numSheets = 2 To numSheets = 79

        wb.Worksheets(numSheets).Range("B3:B195").Copy _
            wb.Worksheets("Sheet 2").Cells(1, columnCounter)

        columnCounter = columnCounter + 1

    Next numSheets

End Sub

Answer 2

Assuming you have Sheet 2 at the back (the last sheet)

Sub Test()
    Dim ws As Worksheet
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet 2")

    For i = 1 To 78
        ws.Range("A1:A193").Offset(0, i - 1) = ThisWorkbook.Sheets(i).Range("B3:B195").Value
    Next i
End Sub

Answer 3

Untested

Sub Sample()
    Dim ws As Worksheet
    Dim i As Long

    Set ws = ThisWorkbook.Sheets(1)

    For i = 2 To 79
        ThisWorkbook.Sheets(1).Range( _
                                     Split(Cells(, i - 1).Address, "$")(1) & _
                                     "2:" & _
                                     Split(Cells(, i - 1).Address, "$")(1) & _
                                     "195" _
                                     ).Value = _
        ThisWorkbook.Sheets(i).Range("B2:B195").Value
    Next i
End Sub

FOLLOWUP (From Comments)

Sub Sample()
    Dim ws As Worksheet
    Dim i As Long

    Set ws = ThisWorkbook.Sheets(1)

    For i = 2 To 79
        '~~> Get Values from A1
        ThisWorkbook.Sheets(1).Range( _
                                     Split(Cells(, i - 1).Address, "$")(1) & _
                                     "1" _
                                     ).Value = _
        ThisWorkbook.Sheets(i).Range("A1").Value

        '~~> Get the column Values
        ThisWorkbook.Sheets(1).Range( _
                                     Split(Cells(, i - 1).Address, "$")(1) & _
                                     "2:" & _
                                     Split(Cells(, i - 1).Address, "$")(1) & _
                                     "195" _
                                     ).Value = _
        ThisWorkbook.Sheets(i).Range("B2:B195").Value
    Next i
End Sub