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

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