Results 1 to 4 of 4

Thread: VBA to sort data across multiple worksheets (same range)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2011
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to sort data across multiple worksheets (same range)

    Hi all

    As usual I have no idea where to start on this one.

    I have a workbook with a number of sheets in that I want to perform a sort on all of them.

    I would like to sort the data in A29:K100 (No headers) by column B (ascending) across all worksheets in the workbook. I don't know how many there will be as people may add sheets at any time however the range will always be the same.

    Hope that makes sense and as always any help appreciated.

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,002
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA to sort data across multiple worksheets (same range)

    Try:
    Code:
    Sub SortSheets()
        Application.ScreenUpdating = False
        Dim ws As Worksheet
        For Each ws In Sheets
            ws.Sort.SortFields.Clear
            ws.Sort.SortFields.Add Key:=Range("B29:B100"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            With ws.Sort
                .SetRange Range("A29:K100")
                .Header = xlNo
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        Next ws
        Application.ScreenUpdating = True
    End Sub
    Last edited by mumps; May 20th, 2019 at 11:03 AM.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Dec 2011
    Posts
    54
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to sort data across multiple worksheets (same range)

    Thanks Mumps.

    Not sure I understand it all but works perfectly.

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,002
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    5 Thread(s)

    Default Re: VBA to sort data across multiple worksheets (same range)

    You are very welcome. The macro just loops through all the sheets and sorts each one based on the range you mentioned.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •