How can I sort the new data in every new sheet by the first column?

Question

After having made several new sheets with data from the first few sheets, I want the macro to sort the data on the new sheet alphabetically. The problem seems to be the fact that the size of this new data is not always the same.

Dim New_Products As Range
Dim New_Sheets As Worksheet
[...]
For Each New_Sheets In Worksheets
    If New_Sheets.Name <> "Sheet1" And New_Sheets.Name <> "Sheet2" Then
        New_Sheets.Activate
        Set New_Products = Range("A1", Range("A1").End(xlToRight).End(xlDown))
        New_Sheets.Sort.SortFields.Clear
        New_Sheets.Sort.SortFields.Add2 Key:=Range("A2", Range("A2").End(xlDown)), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With New_Sheets.Sort
            .SetRange Range("A1", Cells(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column))
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
Next New_Sheets

When I try to run the macro, I get:

Run-time error '1004'
The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank.

Answer 1

Try this code:

Sub SortWSs()
    Dim New_Products As Range, New_Sheets As Worksheet
    For Each New_Sheets In Worksheets
        Select Case New_Sheets.Name
            Case "Sheet1", "Sheet2"
            Case Else
                Set New_Products = New_Sheets.Range("A1").CurrentRegion
                With New_Sheets.Sort
                    .SortFields.Clear
                    .SortFields.Add2 Key:=New_Products.Columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    .SetRange New_Products
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                End With
        End Select
    Next New_Sheets
End Sub