I have a workbook of about 40 sheets. I am working inside a table on sheet1 compiling data from throughout the workbook.

Each cell value in column A has a corresponding sheet of the same name somewhere inside the workbook.

I am attempting to loop through the column and for each cell 1) find the corresponding worksheet 2) copy a single cell value 3) paste that value back to the righthand side of the original table.

My current attempt is below. I keep getting (Error 91) Object variable not set. Any debugging tips or general advice would be greatly appreciated!

```
Sub LoopColumn()

Dim cell As Range
Dim ws As Worksheet
Dim ws_num As Integer
Dim CellName As String
Dim CellLocation As Range

ws_num = ThisWorkbook.Worksheets.Count
Worksheets(1).Select

For Each cell In Range("A:A")
    CellName = ActiveCell.Value
    CellLocation = ActiveCell.Address

        For i = 1 To ws_num

            If ws.Name = CellName Then
                ThisWorkbook.Worksheets(i).Activate

                ActiveSheet.Range("L1").Select
                Selection.Copy
                ActiveSheet.Paste Destination:=Worksheets(1).Range(CellLocation.Offset(0, 4))

            End If
        Next i

    Worksheets(1).Select
Next cell

End Sub

```