How to sort range on specified worksheet?

Question

I am trying to apply VBA code to really large sheets. The idea is that the "My Portfolio.xlsx" filename used below will become a variable so I can run the code against any sheet without having to copy-paste the macro into each sheet.

I can convert a text column to a number datatype.

Sub ConvertTextToNumber()
Workbooks("My Portfolio.xlsx").Sheets("Sheet1").Range("A2:A9999").NumberFormat = "General"
End Sub

This works even if I change the filename. So the general format should be correct.

I am trying to sort the first column (with header) from smallest to largest.

Sub SortSmallestToLargest()
Workbooks("My Portfolio.xlsx").Sheets("Sheet1").Range("A2:BT9999").Sort Key1:=Range("A:A"), Order1:=xlAscending
End Sub

I've adjusted the Sort part multiple times, changing the Range from "A:A" to "A" to "A2", but none of them work.
I've also tried saying Header=Yes or Header=No, but it always errors.

I get

Run-time error '1004': Application-defined or object-defined error

My searches for that error usually refer to giving it out-of-bounds ranges or something else, but everything in the code above should be fine.

I'm thinking the issue stems after the 'Sort' part begins, but no matter what I change or remove, I can't get it to sort the "A" column.

You always have to fully qualify the range. I mean, in your case you try sorting a range of "Sheet1" but using a key from the active sheet. It will work as expected, only if the "Sheet1" is the active one... It should be good to declare a Worksheet variable, let us say, ws, setting it as the sheet you need and using it as ws.Range("A2:BT9999").Sort Key1:=ws.Range("A:A"), Order1:=xlAscending — FaneDuru
– FaneDuru, Commented May 26, 2021 at 8:04

Amiga500 · Accepted Answer · 2021-05-26 08:01:02Z

1

Try this:

Sub SortSmallestToLargest()
    Workbooks("My Portfolio.xlsx").Sheets("Sheet1").Range("A2:BT9999").Sort Key1:=Workbooks("My Portfolio.xlsx").Sheets("Sheet1").Range("A:A"), Order1:=xlAscending
End Sub

Or perhaps more readable:

Sub SortSmallestToLargest()
    with Workbooks("My Portfolio.xlsx").Sheets("Sheet1")
        .Range("A2:BT9999").Sort Key1:= .Range("A:A"), Order1:=xlAscending
    end with
End Sub

Note, you need to have the dot in the .Range() assignment. I just tripped on that.

edited May 26, 2021 at 8:01

answered May 26, 2021 at 7:55

Amiga500

1,2851 gold badge7 silver badges12 bronze badges

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

1 Comment

user5504808 Over a year ago

Perfect! I had to re-specify the workbook in the Sort apparently. At least I wasn't super far off. Thanks a lot for the help!

SmackedHard · Accepted Answer · 2021-05-26 22:42:58Z

0

If I understand correctly, your goal is to run a Macro on any workbook without having to copy code into that workbook.

The two best ways I've found to do this are:

Use the your personal macro workbook, this is a workbook that excel always opens in the background. So you can store code there and it's always available to run. I generally reference the active workbook in my macros.
Save the workbook as an add-in.

I prefer this method as it allows me to share macros or easily remove. Also the personal macro workbook can behave unusually sometimes.

Finally by activating the Macro from the quick access toolbar you ensure the currently active workbook is the untended workbook.

answered May 26, 2021 at 22:42

SmackedHard

471 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to sort range on specified worksheet?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related