VBA code to copy and paste data in one sheet from multiple sheet and loop through

Originally Posted by BadlySpelledBuoy

Sub ConsolidateData()
    Dim ws As Worksheet
    
    Sheets.Add.Name = "allyears"
    
    With Sheets("year2006")
        .Range("A10:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy
    End With
    
    Sheets("allyears").Range("A1").PasteSpecial xlPasteValues
    
    For Each ws In ThisWorkbook.Sheets
        With ws
            If ws.Name <> "allyears" Then
                .Range("B11:B" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy
                With Sheets("allyears")
                    .Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteValues
                    .Cells(1, Columns.Count).End(xlToLeft).Offset(, 1) = ws.Name
                End With
            End If
        End With
    Next ws
    
    Sheets("allyears").Columns.AutoFit
End Sub

BSB

Thank you. I just tired to understand the code you sent understood every bit of it. Thank you !