i have a book set up with a few sheets with info seperated out and then a sheet contianing everything. i have a macro set up to copy and paste from one sheet into a collection sheet, and then go thru and sort all the sheets.

in my macro, i have a section for each indivitual sheet. i want to make it so it goes thru and just performes the task for all of them. i need this done so if we add more sheets in later, the macro doesnt have to be edited...

below is my macro





Sub Macro1()
' Keyboard Shortcut: Ctrl+z

    ActiveSheet.Cells(Rows.Count, "A").End(xlUp).EntireRow.Copy
    Worksheets("ALL").Cells(Rows.Count, "A").End(xlUp).Offset(1).PasteSpecial
    Application.CutCopyMode = False
    
    Sheets("ALL").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
    Sheets("BMI").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Sheets("ODC").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Sheets("SNR").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Sheets("XAC").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    Sheets("DPY").Select
    Cells.Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    
End Sub