Sort Ascending by specific column some sheets excel vba

0

Hi

I'm trying to apply code to a workbook that has plenty other code going on already.

The workbook has a hidden sheet "Customers" and  a frontend "Main" which cannot be sorted.

The sheets are named and I can apply macros to individual sheets, however I need to apply the macro to all except those specified.
Columns are named using enums.

I have code that deletes the contents of the sheets which I'm trying to modify to sort the sheets.

The first set of code below is in the "Main" sheet.

I hope to get this to work. Any help will be appreciated.

Private Sub Sort_Sheets(Optional ByVal FirstSheetOnly As Boolean)
    
' This will sort the specified sheets of the workbook
' "Not Identified" MUST be listed in first position
   
Const SheetsToSort As String = "Not Identified,Set1,Set2,Set3,Set4" & _
                                        "Set5"
   
    Dim Sn() As String
    Dim ws As Worksheet
    Dim Rng As Range
    Dim Rl As Long
    Dim i As Long
   
       
    Application.ScreenUpdating = False
    Sn = Split(SheetsToSort, ",")
    For i = LBound(Sn) To UBound(Sn)
        SortRange Sn(i)
        If FirstSheetOnly Then Exit For
    Next i
   
      Application.ScreenUpdating = True
End Sub

Then in a module I have the following.

Sub SortRange(Sn As String)
    Dim ws As Worksheet
    Dim Rng As Range
    Dim Rl As Long
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets(Sn)
    If Err = 0 Then
        Rl = LastRow(NrpKpiA, ws)
        If Rl >= NrpFirstDataRow Then
            Set Rng = Range(ws.Rows(NrpFirstDataRow), ws.Rows(Rl))
            Range("A2:M500").Sort , _
        Key1:=Range("K1"), _
        Order1:=xlAscending
        End If
    End If
End Sub

I have looked everywhere and cannot find a solution.

Can somebody please help?

Answer
Discuss

Answers

0
Selected Answer

As you are looping through the worksheets, each one in turn is assigned to the variable wsheets. Your code does nothing with these sheets. Instead, it selects the worksheet "Set1", thereby making it the ActiveSheet, and then proceeds to sort the Activesheet - many times over.

The flaw is in the logic, of course. You don't want to sort the ActiveSheet. You want to sort "wsheet", whichever one that happens to be at the time. Therefore you might consider activating "wsheet" instead of "Set1", and that would actually work.

However, it sounds convoluted to make each sheet active so that you can sort the ActiveSheet, and it is. Once you have a handle on a sheet you can do with it whatever you like including activating it, sorting it, deleting it, copying it or modifying any cell in it. Of all those, and more, you are interested only in sorting. That is what the code below does.

Option Explicit

Sub SortByKAscending()
    ' 25 Jan 2018
    Dim Ws As Worksheet
    Dim SortRange As Range
    Dim Rl As Long
    Dim i As Integer
    
    Application.ScreenUpdating = False
    For Each Ws In ActiveWorkbook.Worksheets
        With Ws
            Application.StatusBar = "Sorting sheet " & .Name
            If .Name <> "Main" And .Name <> "Customers" Then
                ' determine the last used row in sort column
                Rl = .Cells(.Rows.Count, "K").End(xlUp).Row
                Set SortRange = Range(.Cells(2, "A"), .Cells(Rl, "M"))
                With SortRange
                    .Sort Key1:=.Cells(11), Order1:=xlAscending
                    .Cells.WrapText = True
                    .Rows.EntireRow.AutoFit
                End With
                i = i + 1
            End If
        End With
    Next Ws
    With Application
        .ScreenUpdating = True
        .StatusBar = ""
    End With
    MsgBox i & " worksheets were sorted.", _
           vbInformation, "Sorting complete."
End Sub
Discuss

Discussion

Thank you for your reply. I was hoping you could help me recode this though.
I know the code works on the active sheet and doesn't go through each sheet.
As I understand it, the first bit does uses the first sheet and loops through all the sheets except the ones specified.
The additional code then runs the sort.
Then the last bit moves onto the next sheet.

I have other code that actually goes through all the sheets, and clears the contents of the sheets. I will try modify it to do what I want.
Shaun1 (rep: 4) Jan 24, '18 at 1:17 am
I did re-code your procedure and had been hoping you would at least try. Read the last sentence of my above post.
Variatus (rep: 1610) Jan 24, '18 at 4:09 am
Thanks, I see what you mean.
The source data that I use is another excel document, which I import into the one I work with. So I was actually trying to automate the sorting on import or alternately have a button on the Main page linked to a Macro to apply to all the sheets except the specified ones.
Sorting only happens once a day after importing new data.
Every week the Workbook is cleared and a new days data is imported.
Shaun1 (rep: 4) Jan 24, '18 at 6:26 am
I can't get the suggested code to work.
Error 1004.
Is this supposed to be in a module or should it be in ThisWorkbook?
I need it also to autofit row hight after the sorting for each of the sheets.
Range("J2:J500").Select
    Selection.Rows.AutoFit
Shaun1 (rep: 4) Jan 24, '18 at 8:06 am
You don't tell me on which line of code the error occurs but a likely candidate is your autofit addition. You can't adjust the row height only in column J. As I explained, you don't need to select anything but you should pay attention to which sheet you address (ActiveSheet by default if nothing is mentioned, which you don't want to do in a loop). 
I have re-worked the code above to dynamically set the range to sort and incorporate your request for row height adjustment. I also added an indicator in the statusbar to show which sheet is currently processed and a final message when all is done.
If you still get error 1004 look at the status bar to find out which sheet is causing the error and look at the code with the same intent.
Variatus (rep: 1610) Jan 24, '18 at 10:49 pm
Hi
Thanks. It sort of works, but messed up my spreadsheet row hights. Thanks for backups :) .
The contents of column J is where the text is either a short or long entry that wraps.That is why I selected column J.
I understand the part where you specified .Rows.EntireRow.AutoFit but this does mess with the layout. The text in column J must wrap, but nothing else as the spreadsheet has been setup for readability with everything nicely spaced.
Nice that it displays another message when done.
Could you suggest a solution for the row height using column J. The first row is headers/column lables.
Shaun1 (rep: 4) Jan 25, '18 at 4:08 am
Please try this idea: Autofit will have no effect if the WrapText property is set to False. The code snippet below first sets this property to False for all the sort range, then sets it to True for column J before applying AutoFit to all rows in the range.
                With SortRange
                    .Sort Key1:=.Cells(11), Order1:=xlAscending
                    .Cells.WrapText = False
                    .Columns(10).Cells.WrapText = True
                    .Rows.EntireRow.AutoFit
                End With
 
Variatus (rep: 1610) Jan 25, '18 at 5:04 am
Thank you.
This solved my problem. I have a new problem to sove, but it's unrelated.
Great work. Much appreciated.
Shaun1 (rep: 4) Jan 26, '18 at 4:20 am
Bring in on Shaun. I look forward to your next question. The weekend is long, and it's cold outside.
Variatus (rep: 1610) Jan 26, '18 at 6:08 am
Hi
Seems I still have a problem with this code.
Row 1 of some of those sheets is getting AutoRowHeight resized too.
I have row 1 set at 42, as I have some button shortcuts in A1 and some text for headings.
It is not the active sheet, as I have an ActiveX button on the Main sheet calling the macro to sort.
When I go through the sheets after sorting, there are random sheets that the first row has resized. Not sure why. Any ideas?
Edit: I figured out why. If the sheet has no data in rows 2 and below, it resizes row 1.
Thanks in advance.
Shaun1 (rep: 4) Jan 30, '18 at 5:03 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login