VBA sorting table in other workbook

Asked May 24 '18 at 14:14

Active May 24 '18 at 14:14

Viewed 140 times

Good morning,

I have a macro that, among other things, sorts a table in another workbook. Here's the relevant bits of code

Set wbLSHP = Workbooks("CDU_Enrollee Engagement Tracking Report v2.2.xlsx")
Set wsLSHP = wbLSHP.Worksheets("Sheet1")

"wsLSHP" is the worksheet where the table is located. The table is named "Table1"

wsLSHP.ListObjects("Table1").Sort.SortFields.Clear
wsLSHP.ListObjects("Table1").Sort.SortFields.add _
    Key:=Range("Table1[[#All],[CHW First Name]]"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, DataOption:=xlSortNormal
With wsLSHP.ListObjects("Table1").Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

When running this, I get the following error: "Run-time error '1004':, Application-defined or object-defined error

This happens at the following line:

wsLSHP.ListObjects("Table1").Sort.SortFields.add _
Key:=Range("Table1[[#All],[CHW First Name]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

Any ideas on what's causing this error?

edited Jul 09 '18 at 19:34

Community

asked May 24 '18 at 14:14

Dustin Burns

Is the `wsLSHP` opened, when you are getting the error? – Vityata May 24 '18 at 14:16
The file is open. I'm getting the error at the line listed above, when the macro attempts to sort the table. – Dustin Burns May 24 '18 at 14:23
What is the idea of this - `("Table1[[#All],[CHW First Name]]")` – Vityata May 24 '18 at 14:27
1

Oh that's probably why it's happening. That came from recording the macro in the wsLSHP sheet, but since this is being run in a different workbook maybe it can't process that range. I'll try to change it and see what happens. – Dustin Burns May 24 '18 at 14:30
1

Yep, added a dynamic range instead ("Table1[[#All],[CHW First Name]]") and that did the trick. Thanks for the help! – Dustin Burns May 24 '18 at 14:39
You are welcome :) – Vityata May 24 '18 at 14:41

VBA sorting table in other workbook

0 Answers0