Need VBA Code to automatically sort on a numerical column highest to lowest when pasting associated data.

[JMC57]

I have an Excel Sheet with columns A through J. There are 1900 rows. The Numerical data to drive the sorting is in Column J. There are formulas in Col K-O. The cells are protected, and these are the same formulas that are copied down and locked.

I need a VBA code to sort rows based on Col J highest to lowest numerical value without impacting the sort of Col K-O

Any help, suggestions or solutions will be appreciated.

 

[skillilea]

not sure if you have passwords on the protection but, try this

Sub SortData()
    Dim ws As Worksheet
    Dim lR As Long
    
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' your sheet
    
    ws.Unprotect
    
    lR = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
    
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("J2:J" & lastRow), SortOn:=xlSortOnValues, _
            Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("A1:O" & lastRow)  ' assuming A as start of columns
        .Header = xlYes  ' assuming you have headers
        .Apply
    End With
    
    ws.Protect
End Sub

 

[JMC57]

Thank you for your response. The last time a worked with VBA was 2013 so I am very rust and was never really that good at it.
Does it matter if I have password protection? I have it when I lock the sheets ?

Set ws = ThisWorkbook.Worksheets("Sheet1") ' your sheet Do I keep ‘your sheet?
lR = ws.Cells(ws.Rows.Count, "J"). Do I change “J” to “J1900”? since there are 1900

.SortFields.Add Key:=Range("J2:J" & lastRow), Do I change “J” to “J1900”? since there are 1900 rows
SetRange Range("A1:O" & lastRow) Do I change tio A2 and O1900

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need VBA Code to automatically sort on a numerical column highest to lowest
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[JMC57]

Thank you for your response. The last time a worked with VBA was 2013 so I am very rust and was never really that good at it.
Does it matter if I have password protection? I have it when I lock the sheets ?

Set ws = ThisWorkbook.Worksheets("Sheet1") ' your sheet Do I keep ‘your sheet?
lR = ws.Cells(ws.Rows.Count, "J"). Do I change “J” to “J1900”? since there are 1900

.SortFields.Add Key:=Range("J2:J" & lastRow), Do I change “J” to “J1900”? since there are 1900 rows
SetRange Range("A1:O" & lastRow) Do I change tio A2 and O1900

not sure if you have passwords on the protection but, try this

Sub SortData()
    Dim ws As Worksheet
    Dim lR As Long
   
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' your sheet
   
    ws.Unprotect
   
    lR = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
   
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("J2:J" & lastRow), SortOn:=xlSortOnValues, _
            Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("A1:O" & lastRow)  ' assuming A as start of columns
        .Header = xlYes  ' assuming you have headers
        .Apply
    End With
   
    ws.Protect
End Sub

I entered the suggested code as
Sub SortData()
Dim ws As Worksheet
Dim lR As Long

Set ws = ThisWorkbook.Worksheets("2Cut And Paste ABC report")

ws.Unprotect

lR = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row

With ws.sort
.SortFields.Clear
.SortFields.Add Key:=Range("J2:J1999" & lastRow), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
.SetRange Range("A1:O" & lastRow)
.Header = xlYes
.Apply
End With

ws.Protect
End Sub

I get a Run-Time error 9 Subscript out of rance

 

[JMC57]

I have an Excel Sheet with columns A through J. There are 1900 rows. The Numerical data to drive the sorting is in Column J. There are formulas in Col K-O. The cells are protected, and these are the same formulas that are copied down and locked.

I need a VBA code to sort rows based on Col J highest to lowest numerical value without impacting the sort of Col K-O

Any help, suggestions or solutions will be appreciated.

Note this code is also posted at
Need VBA Code to automatically sort on a numerical column highest to lowest

 

[skillilea]

Try this, sorry my variable was mislabled

Sub SortData()
    Dim ws As Worksheet
    Dim lR As Long
   
    Set ws = ThisWorkbook.Worksheets("Sheet2") ' your sheet
   
    ws.Unprotect
   
    lR = ws.Cells(ws.Rows.Count, "J").End(xlUp).Row
   
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("J2:J" & lR), SortOn:=xlSortOnValues, _
            Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("A1:O" & lR)  ' assuming A as start of columns
        .Header = xlYes  ' assuming you have headers
        .Apply
    End With
   
    ws.Protect
End Sub