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

Originally Posted by BadlySpelledBuoy

Maybe this?

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

BSB

Thank you. This code works but the it repeats copying column A data repetedly also instead of pasting data horizontally it paste data after under let's say when pasting data for 2006 is done it put the ddata for 2007 right below it