CODEWITHSUNDEEP
excelvba
MsAgentM
MsAgentM

Reputation: 153

Application errors when sorting in excel vba

I am trying to get data to sort with VBA. This range will change so it needs to be determine each time the macro is run. If I use the code below, I get a 1004 application error.

Sub CaseRevToDo()

Set WBToDo = ThisWorkbook.Worksheets("ToDo")
Set TblToDo = WBToDo.ListObjects("Table11")
Set WBConReport = Workbooks.Add
WBToDo.Activate

WBToDo.ListObjects("Table11").Range.AutoFilter Field:=8, Criteria1:="<=" & Date + 30, _
   Operator:=xlOr, Criteria2:="Overdue"

WBToDo.Application.Union(Columns(2), Columns(3), Columns(9)).Copy

WBConReport.Worksheets("Sheet1").Range("A:C").PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks _
    :=True, Transpose:=False
WBConReport.Worksheets("Sheet1").Columns("A").ColumnWidth = 20
WBConReport.Worksheets("Sheet1").Columns("C").ColumnWidth = 10

WBConReport.Worksheets("Sheet1").Range("A9", Range("C9").End(xlDown)).Sort key1:=Range("C9"), order1:=xlAscending, Header:=xlNo  *** Error on this line


End Sub

I have tried to specify the range for this worksheet like below:

WBConReport.Worksheets("Sheet1").Range("A9:C114").Sort key1:=Range("C9"), order1:=xlAscending, Header:=xlNo

But then I get an error saying that my sort reference is not valid. The issue seems to be how I am referencing the range, but I can't find it.

Upvotes: 0

Views: 255

Answers (1)

Michal
Michal

Reputation: 5848

With WBConReport.Worksheets("Sheet1")
    .Range("A9", .Range("C9").End(xlDown)).Sort key1:=Range("C9"), order1:=xlAscending, Header:=xlNo
End With

You have to specify ("qualify") exactly where each range belongs to. In your code, you have to explicitly specify that "A9" and "Range("C9").End(xlDown)" belong to BConReport.Worksheets("Sheet1")

Upvotes: 1

Related Questions