How do I modify a macro so it sorts?

Question

We have a small Book Club. We read the book and then score it.
To keep track of the books I use a macro that takes the scores adds the latest book and then sorts the list from highest to lowest points.
I have to manually update the fields with each new book.

I tried to automate the process recently by using:
Meaning of .Cells(.Rows.Count,“A”).End(xlUp).row which counts the number of empty cells upwards until it reaches a cell with some data in it and then performs the operation on it.
The modified macro works until it tries to sort the list and it then stops working.

How I can modify the sort?

This is the Book Club file: Page 1.

This is the Book Club file: Page 2.

This is the modified macro which works until the sort then stops.

Sub PositionIndex()
 '
' PositionIndex Macro
' This macro sorts the ongoing position of the most popular books.
'
' Keyboard Shortcut: Ctrl+Shift+X

Dim wb As Workbook
Dim ws As Worksheet
Dim symbol As String
Dim n As Integer
Dim lastrow As Long

Sheets("Position Series").Select
Find the last used row in a Column: column K in this example

    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
    End With
    
    MsgBox lastrow
       
    Dim DataRange As Range

    Set DataRange = Range("C7:K" & lastrow)
    DataRange.Select
    Selection.ClearContents
 
    Sheets("Time Series").Select
    'Find the last used row in a Column: column "Q" in this example
    
     With ActiveSheet
        lastrow = .Cells(.Rows.Count, "Q").End(xlUp).Row
    End With
    
    MsgBox lastrow
    
    Range("Q9:Q" & lastrow).Select
    Selection.Copy
    
    Sheets("Position Series").Select
    Range("K7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Sheets("Time Series").Select
    'Find the last used row in a Column: column "I" in this example
    
     With ActiveSheet
        lastrow = .Cells(.Rows.Count, "I").End(xlUp).Row
    End With
    
    MsgBox lastrow
    
    Range("C9:I" & lastrow).Select
    Selection.Copy
    Sheets("Position Series").Select
    Range("C7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("Position Series").Select

    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
    End With
    
    MsgBox lastrow
    
    Range("C7:K" & lastrow).Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Position Series").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Position Series").Sort.SortFields.Add Key:=Range( _
        "K7:K" & lastrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Position Series").Sort
        .SetRange = Range("C7:K" & lastrow).Select
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2").Select
    Sheets("Time Series").Select
    Range("A2").Select
    
End Sub

Answer 1

I really really like this little project you have :)! I took some time and rewrote the code so you can see what is happening and how it can be a bit more stable and efficient.

I think I understand what you want to achieve.

1 . - You want to define the range (column K) where the rankings are:

ActiveWorkbook.Worksheets("Position Series").Sort.SortFields.Add Key:=Range( _
        "K7:K" & lastrow), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal

2 . - And then sort the columns to the right (Date, Title, Author, Proposer) of the defined range.

With ActiveWorkbook.Worksheets("Position Series").Sort
        .SetRange = Range("C7:K" & lastrow).Select
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

These two parts can be rewritten as:

   Postion_Series.Range("C7:K" & lastrow).Sort key1:=Range("K7:K" & lastrow), _
   order1:=xlDescending, Header:=xlNo

---

I see you have several of the following lines:

Sheets("Position Series").Select

And you don't need that.

So first we define your worksheets:

Dim Time_Series As Worksheet
Dim Postion_Series As Worksheet
Set Time_Series = ActiveWorkbook.Worksheets("Time Series")
Set Postion_Series = ActiveWorkbook.Worksheets("Position Series")

So every time we use the variable Time_Series we are referring to the excel sheet "Time Series". Therefore we can rewrite the code:

Sheets("Position Series").Select
'Find the last used row in a Column: column K in this example

    With ActiveSheet
        lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
    End With

To a more stable and efficient code, because you always want in this part the ActiveSheet to be the "Postion Series" worksheet, and we can call our variable Postion_Series for that:

With Postion_Series
    lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
End With

What is happening is that we are telling your code that your line of code should be applied to the variable Postion_Series = as saying "direct me to the worksheet "Position Series" and execute the code" i.e. Sheets("Position Series").Select.

---

Full Revised Code:

Sub PositionIndex()
 '
' PositionIndex Macro
' This macro sorts the ongoing position of the most popular books.
'
' Keyboard Shortcut: Ctrl+Shift+X

Dim wb As Workbook
Dim Time_Series As Worksheet
Dim Position_Series As Worksheet
Dim symbol As String
Dim n As Integer
Dim lastrow As Long

Set Time_Series = ActiveWorkbook.Worksheets("Time Series") 'Define the worksheet "Time Series" to a variable
Set Position_Series = ActiveWorkbook.Worksheets("Position Series") 'Define the worksheet "Position Series" to a variable

'Find the last used row in a Column: column K in this example
    With Position_Series
        lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row 'Find the last row for Column K in the worksheet "Position Series"
    End With
    
    MsgBox lastrow
       
    Dim DataRange As Range

    Set DataRange = Position_Series.Range("C7:K" & lastrow + 50) 'Define the datarange in the Sheet "Position Series" to clear the old data
    DataRange.ClearContents 'Clear the data for the defined range
 
'Find the last used row in a Column: column "Q" in this example
    With Time_Series
        lastrow = .Cells(.Rows.Count, "Q").End(xlUp).Row 'Find the last row for Column Q in the worksheet "Time Series"
    End With
    
    MsgBox lastrow
    
    'Copy column with Score rankings (column Q) from worksheet "Time Series" and paste it to "Position Series" for the column K, "Position"
    Time_Series.Range("Q9:Q" & lastrow).Copy 'Copy column until the last value
    'Paste the column to the new place
    Position_Series.Range("K7").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    'Find the last used row in a Column: column "I" in this example
     With Time_Series
        lastrow = .Cells(.Rows.Count, "I").End(xlUp).Row 'Find the last row for Column I in the worksheet "Time Series"
    End With
    
    
    MsgBox lastrow
    'Copy relevant data about the Date, Title, Author, Propser for from the "Time Series" to the "Position Series"
    Time_Series.Range("C9:I" & lastrow).Copy
    'Paste it to the new place
    Position_Series.Range("C7").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False 'This line will deselect the copy range. I think you should move to hear to deselect the copy range as soon you have pasting your data. So I moved the line to here.
    
    'Find the last row for Column K to decide which row to sort
    With Position_Series
        lastrow = .Cells(.Rows.Count, "K").End(xlUp).Row
        MsgBox lastrow
    End With

    Position_Series.Activate    
        'Sort the range "Range("C7:K" & lastrow)" by the values of column K. From Largest to Smallest, and the first row is not header.
        Position_Series.Range("C7:K" & lastrow).Sort key1:=Position_Series.Range("K7:K" & lastrow), _
        order1:=xlDescending, Header:=xlNo
    
End Sub