Sorting Columns using Code

[boylejob]

Good Afternoon,

I am in need of some help. I have a tab on a worksheet and I would like sort the first 9 columns of information. The sort order is going to by columns I then B then C.

The macro recorder gave me this

Please Login or Register  to view this content.

but I don't want to SELECT anything so I tried rewriting it to this

Please Login or Register  to view this content.

where wsList is

Please Login or Register  to view this content.

and I end up with an error indicating the sort reference is not valid.

I am almost positive I have done this before, but I have been unable to locate an example in any of my previously written code. Or I might just have imagined that I have done this before without SELECTING anything.

Any suggestion?

[Leith Ross]

Hello Jeff,

Have you checked that the worksheet "List" is the ActiveSheet in the workbook?

Sincerely,
Leith Ross

[boylejob]

Leith,

Can you make a sheet the active sheet without having to SELECT it?

It may not be a big deal to select it. When I first joined this forum my goal was to improve the speed at which my code ran. The first thing I was told was to get away from selecting things. I laid that to heart and have pretty much avoided selecting things every since. The speed of my programs increased incredibly.

This is a single operation so using select is not going to really slow anything down. Just wanted to see if their was another way of doing this.

Thanks!

[Leith Ross]

Hello Jeff,

Is this code in and operating only on the worksheets in the Active Workbook "SEOG Data.xls"?

Sincerely,
Leith Ross

[boylejob]

Leith,

No, I am currently opening one other workbook and will probably open at least one more before this code is finished.

Here is all of the code for the subroutine I am working on.

Please Login or Register  to view this content.

I definitely appreciate your help on this. Like I mentioned before it is probably no big deal to use select in this case, but I am just wanting to see if there is a way of doing it without selecting. If you think not using select will be more trouble than its worth, just let me know and I'll go ahead and use that.

[Leith Ross]

Hello Jeff,

I spotted a few things that are causing you problems.

In this Find routine change the ActiveCell to wsISIR.Cells(1, 1)

Please Login or Register  to view this content.

In this Find routine change ActiveCell to wsNCSIG.Cells(1, 1)

Please Login or Register  to view this content.

Whenever you open a workbook, that workbook becomes the ActiveWorkbook.

Please Login or Register  to view this content.

The code in read can be written as...

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[T-J]

but I don't want to SELECT anything so I tried rewriting it to this

Please Login or Register  to view this content.

and I end up with an error indicating the sort reference is not valid.

I am almost positive I have done this before, but I have been unable to locate an example in any of my previously written code. Or I might just have imagined that I have done this before without SELECTING anything.

Any suggestion?

For the sorting, just a simple amendment is required.
For each Sort Key, insert the worksheet before each range:

Please Login or Register  to view this content.

Then any sheet can be active and nothing needs selecting and the sort will still work.

[boylejob]

Leith and T_J,

Thank you so much! Program is working great now!

I don't know how many times I have stated how much I love this forum. You NEVER EVER visit this forum without learning something new. It is amazing how much better my coding has gotten since joining this forum.

Thanks again for the help!