Application errors when sorting in excel vba

Question

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.

You should also qualify the workbook/worksheet that Columns are in/on in Columns(2), Columns(3), Columns(9). — BigBen
– BigBen, Commented Jul 1, 2020 at 15:46
@BigBen, ok. Can you explain what you mean exactly by "Qualify"? I set the workbook/worksheet to be the workbook that was added at the beginning of the macro but I must misunderstand what that does. The workbook with the columns are set to a different workbook. — MsAgentM
– MsAgentM, Commented Jul 1, 2020 at 18:52
key1:=Range("C9")... "qualify" meaning specify the workbook/worksheet that Range("C9") is on. Here you have not specified, so there is an implicit ActiveSheet. — BigBen
– BigBen, Commented Jul 1, 2020 at 18:54
Best practice whenever you use Range or Cells or Rows or Columns is to make sure there is a reference to the worksheet/workbook they are in/on. That is "qualifying." — BigBen
– BigBen, Commented Jul 1, 2020 at 18:55

Michal · Accepted Answer · 2020-07-02 04:16:52Z

1

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")

answered Jul 2, 2020 at 4:16

Michal

7,6744 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Application errors when sorting in excel vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related