Sorting multiple spreadsheets

  • Hello, I'm new to the forum. I'm looking to create a macro that will 1) delete subtotals, 2) filter by a column, 3) highlight any differences +/- 50, and finally, 4) sort based on two columns.

    The macro is able to do 1-3 without problem, and will do #4 on the sheet on which I am recording the macro, but if I were to apply it to any other sheet, it will not sort. Any assistance is appreciated!

  • Re: Sorting multiple spreadsheets


    ActiveWorkbook.Worksheets("10.19") refers to a specific worksheet.
    You will have to repeat the sort for each worksheet or iterate through the Worksheets() collection (For each ws in Activeworkbook.worksheets...)

  • Re: Sorting multiple spreadsheets


    Is there any reason why you need to use code to conditionally format the cells when you have conditional formatting for that sort of thing? If you were to take that step out and apply a simple loop the code could be written in only a few lines for all sheets.

    Take care


  • Re: Sorting multiple spreadsheets

    Further to Kieran and Smallman's comments

    Sub Sort_Multiple_Sheets()
    Dim j As Long
    For j = 1 To ActiveWorkbook.Sheets.Count
        With Sheets(j)
            .Range("C1:C26").Sort Key1:=.Range("C1"), Order1:=xlAscending, _
            Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        End With
    Next j
    End Sub

    Get rid of the scroll downs (Leftover from the recorder)

    Read up on selecting. It is very seldom required.


    could/should be written as

    Range("A1:M" & Cells(Rows.Count, 1).End(xlUp).Row).RemoveSubtotal

    or, if you have more stuff happening in that range

    With Range("A1:M" & Cells(Rows.Count, 1).End(xlUp).Row)
    End With

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!