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


    HI,


    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


    Hi


    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


    Smallman

  • Re: Sorting multiple spreadsheets


    Further to Kieran and Smallman's comments


    Code
    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.

    Code
    Range("A1:M10000").Select 
        Range("I10").Activate 
        Selection.RemoveSubtotal

    could/should be written as

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

    or, if you have more stuff happening in that range

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

Participate now!

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