Run-time error '1004': Sorting in sheetchange

Question

I have simple problem using a sort macro. When I import data from my other Workbook, the workbook run the macro below in SheetChange and receive the run-time error. HOWEVER, it is working completely fine if I just run the macro when the Workbook is open.

I don´t know if I have to replace Range with ActiveWorkbook or something, but I think I have tried a lot of different stuff, still without luck :(

I want it to sort from column B, starting in row 13 and until the last row/column. It is dynamic, so it will change every time I import stuff.

Sub TESTSORT()
Dim startCol, myCol As String
Dim startRow, lastRow, lastCol As Long
Dim ws As Worksheet
Dim Rng, cell As Range

Application.ScreenUpdating = False
Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
startCol = "A"
startRow = 13
lastRow = ws.Range(startCol & ws.Rows.Count).End(xlUp).Row
'lastCol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
lastCol = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

myCol = GetColumnLetter(lastCol)

Set Rng = ws.Range(startCol & startRow & ":" & myCol & lastRow)

' I GET THE ERROR HERE
Range(.Cells(13, 1), .Cells(lastRow, lastCol)).Sort key1:=Range("B13:B" & lastRow),
order1:=xlAscending, Header:=xlNo

End With
Application.ScreenUpdating = True
End Sub

Function GetColumnLetter(colNum As Long) As String
Dim vArr
vArr = Split(Cells(1, colNum).Address(True, False), "$")
GetColumnLetter = vArr(0)
End Function

Answer 1

It looks like you've got some issues inside your With statement... using Range instead of .Range in several places. I've tried to clean it up a bit below...

Sub TESTSORT()
Dim startCol, myCol As String
Dim startRow, lastRow, lastCol As Long
Dim ws, ws2 As Worksheet
Dim Rng, cell As Range

Set ws2 = ActiveSheet 'Preserves a reference to the active sheet as ws2
ThisWorkbook.Activate 'Makes this workbook the one that is displayed

Application.ScreenUpdating = False
Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
    startCol = "A"
    startRow = 13
    lastRow = .Range(startCol & .Rows.Count).End(xlUp).Row
    'lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
    lastCol = ws2.UsedRange.Columns(ws2.UsedRange.Columns.Count).Column

    myCol = GetColumnLetter(lastCol)

    Set Rng = .Range(startCol & startRow & ":" & myCol & lastRow)

    ' I GET THE ERROR HERE
    .Range(.Cells(13, 1), .Cells(lastRow, lastCol)).Sort key1:=.Range("B13:B" & lastRow),
    order1:=xlAscending, Header:=xlNo

    End With
Application.ScreenUpdating = True
End Sub

Function GetColumnLetter(colNum As Long) As String
Dim vArr
vArr = Split(Cells(1, colNum).Address(True, False), "$")
GetColumnLetter = vArr(0)
End Function