Sort Method: Run time error

HortonPB2

New Member
Joined
Feb 3, 2014
Messages
3
Hello!

I've spent a while now looking online for a solution to my problem, but I'm still getting the same error. I have a range in a worksheet (LAYOUT) that I want sorted first by column B ascending then by column C ascending. I do NOT want to select or activate the worksheet, if that is possible. The code I am trying is this:

Sub Macro4()

Worksheets("LAYOUT").Range("A:D").Sort Key1:=Worksheets("LAYOUT").Columns("B"), Order1:=xlAscending, _ Key2:=Worksheets("LAYOUT").Columns("C"), Order2:=xlAscending, Header:=xlYes

End Sub

Thank you,
Horton
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is the sheet protected? What's the actual error?

Perhaps try:
Code:
With Worksheets("LAYOUT")
.Range("A:D").Sort Key1:=.Range("b1"), Order1:=xlAscending, Key2:=.Range("c2"), Order2:=xlAscending, Header:=xlYes
End with
 

HortonPB2

New Member
Joined
Feb 3, 2014
Messages
3
Hi Rory,

That didn't work, I'm still getting the runtime error.

Thanks,
Horton
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I refer you to my two opening questions then. ;)
 

HortonPB2

New Member
Joined
Feb 3, 2014
Messages
3
LoL...my bad. I saw the code...got excited...copied it...tried it...it failed...I cried.

Only then did I go back and read your two questions. The answer is "yes, the sheet is protected." and that was the issue. Adding:

Sub Macro4()​

Worksheets("LAYOUT").Unprotect
Worksheets("LAYOUT").Range("A:D").Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("C2"), Order2:=xlAscending, Header:=xlYes​
Worksheets("LAYOUT").Protect

End Sub​

Made it work.

I was thrown off because the columns I am selecting were all unlocked. I'll remember next time to unprotect regardless of what I'm doing.

Thanks Rory!!!
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. Welcome to the forum, by the way. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,314
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top