CODEWITHSUNDEEP
vbaexcel
Hwee7
Hwee7

Reputation: 45

sorting for worksheets in 1 workbook

I have searched the net for a macro that can help me to do sorting for worksheets in a workbook and modified it a little ( adding the exclude worksheets)

Sub SortDataWorksheets()

Dim wsh As Worksheet
For Each wsh In ThisWorkbook.Sheets
    If wsh.Name <> "Dashboard" And wsh.Name <> "rawdata" And wsh.Name <> "template" And wsh.Name <> "macros instructions" And wsh.Name <> "Sheet1" _
    And wsh.Name <> "Sheet2" And wsh.Name <> "inputlist" And wsh.Name <> "ProductList" And wsh.Name <> "NA" Then

    'sort columns A to AL based on data in column B
    wsh.Columns("A:AL").Sort key1:=Range("B3"), order1:=xlAscending, Header:=xlYes
    End If
Next

End Sub

However, this doesnt work as excel will throw the 

Run Time error '1004' :
The sort reference is not valid. Make sure that it's within the data you want to sort...

My data starts from Row 3 onwards, 1st 2 rows are headers. How do i exclude the first 2 rows for sorting?

Upvotes: 0

Views: 46

Answers (3)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

I find it easier to read a Select Case rather than multiple IF..AND..THEN when ignoring sheets.

The code below will adjust to how many rows contain data in column B.

I'm still not sure which is the preferred method of sorting - single line, or what the macro recorder returns (similar to below).

Public Sub SortDataWorksheets()

    Dim wsh As Worksheet
    Dim lLastRow As Long

    For Each wsh In ThisWorkbook.Worksheets
        Select Case wsh.Name
            Case "Dashboard", "rawdata", "template", "macros instructions", _
                 "Sheet1a", "Sheet2a", "inputlist", "ProductList", "NA"

                 'Do nothing

            Case Else

                lLastRow = wsh.Cells(wsh.Rows.Count, 2).End(xlUp).Row

                With wsh.Sort

                    With .SortFields
                        .Clear
                        .Add Key:=Range("B5:B" & lLastRow), _
                             SortOn:=xlSortOnValues, _
                             Order:=xlAscending, _
                             DataOption:=xlSortNormal
                    End With

                    .SetRange Range("A5:C" & lLastRow)
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    '.SortMethod = xlPinYin 'Only need if sorting Chinese characters.
                    .Apply

                End With

        End Select
    Next wsh

End Sub

Upvotes: 0

Miqi180
Miqi180

Reputation: 1691

This works for me:

Sub SortDataWorksheets()

    Dim wsh As Worksheet
    Dim LastRow As Long

    For Each wsh In ThisWorkbook.Sheets
        With wsh
            If .Name <> "Dashboard" And .Name <> "rawdata" And .Name <> "template" And _
                .Name <> "macros instructions" And .Name <> "Sheet1" _
                And .Name <> "Sheet2" And .Name <> "inputlist" And _
                .Name <> "ProductList" And .Name <> "NA" Then

                LastRow = .Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

                'sort columns A to AL based on data in column B
                .Range("A2:AL" & LastRow).Sort key1:=.Range("B3"), order1:=xlAscending, Header:=xlYes
            End If
        End With
    Next

End Sub

Notice the use of a specific range instead of columns.

Upvotes: 0

Vityata
Vityata

Reputation: 43585

Change from:

wsh.Columns("A:AL").Sort key1:=Range("B3"), order1:=xlAscending, Header:=xlYes

To:

wsh.Columns("A:AL").Sort key1:=wsh.Range("B3"), order1:=xlAscending, Header:=xlYes

Because if you do not refer to the parent worksheet, VBA takes as parent worksheet the ActiveSheet or the sheet in which the code is. Both would return an error in your case.

Upvotes: 1

Related Questions