Compile Worksheets :

if you have data in same format on multiple worksheets and you want to compile on a single sheets you can Use this Macro,

Sub ConsolidateAllSheets()

   

    Dim wksConsolidate      As Worksheet

    Dim wksSheet            As Worksheet

    Dim lngLastRow          As Long

    Set wksConsolidate = ThisWorkbook.Worksheets.Add

    lngLastRow = 1

    Application.DisplayAlerts = False

    On Error Resume Next

        ThisWorkbook.Worksheets("Consolidated").Delete

    On Error GoTo 0

   

    wksConsolidate.Name = "Consolidated"

    If Not ThisWorkbook.ProtectStructure Then

        With wksConsolidate

            For Each wksSheet In ThisWorkbook.Worksheets

                If Not wksSheet Is wksConsolidate Then

                    wksSheet.UsedRange.Copy .Range("A" & lngLastRow)

                    lngLastRow = .UsedRange.Rows.Count + 1

                End If

            Next wksSheet

        End With

    End If

    MsgBox "Done"

   

End Sub