Trouble sorting a sheet without using the sheet name

[micina]

I'm beating my head against a wall over here.

I recorded a macro that included sorting, but the sheet name is constantly changing, so when I tried to run the macro it kept saying "object out of range". I tried to replace the name of the sheet with the Excel codename "Sheet1" and now it's showing this error: "Run-time error '424': Object required."

The first line here is where the error occurs:

Please Login or Register  to view this content.

What am I doing wrong?

[SUPPO_USN]

I did a sample record macro listed below:

Please Login or Register  to view this content.

You might want to verify that your worksheet name is spelled correctly. Copy/Paste from the tab might be best to ensure name is the same.

Also, in lines 2-3 you are sorting on Range("K1") whereas later you are sorting on Range("A2:K6")

Not sure if these will fix your issue, but might be a place to start looking...

[leelnich]

Try this:

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

[Olly]

Or just:

Please Login or Register  to view this content.

[JordtenBulte]

I'm not sure but if I understand your question correct the name of the worksheet you need to work with is variable. You can solve this with the following code if I'm correct (I'm no expert either):

first declare a variable for the worksheet name:

Dim shtName As String

shtName = ActiveSheet.Name

Now replace "Sheet1" for shtName (don't use " " since you are referencing to a variable and not a static String value)

I hope it will work

[micina]

I replaced the section of code I had originally had with what you wrote, leelnich, and while there was no error that came up, it didn't sort properly. (I'm sorting a column of dates, if that helps.)

[micina]

Tried to use JordtenBulte's suggestion (but changed the suggested name to something more specific to the macro's purpose) and the "Object required" error came up again. Here's what my code looks like now:

Please Login or Register  to view this content.

[leelnich]

OK please confirm:
1) Your intention is to sort the ActiveSheet, that is, the VISIBLE currently-selected sheet in the currently-selected workbook.
2) You intend to sort on column K
3) Column K contains TEXT dates, not NUMERIC dates. (the cell Number Format is set to Text, not Date or Custom)

BTW, forum rules require you to enclose code within code tags. Just select the relevant text and click the # icon on the Edit Ribbon. Thanks!

[micina]

@leelnich

1. Yes
2. Yes
3. Hmm this is probably the issue. Column K is a list of dates that are listed as dates, not as text. However, switching over to text stores it as a garbage list of numbers. In the code, could I switch the dates to text, then sort, then switch back to dates?

[leelnich]

Actually, you WANT numbers. I simply noticed you included DataOption:= xlSortTextAsNumbers in your sort parameters and assumed you had text. As a test, please enter this in a cell somewhere: =ISNUMBER(K5), where K5 is one of your dates. You should get TRUE if it's an Excel date.

Now, all that being said, @Olly's post #4 uses the Range.Sort method. It's an older Sorting method, a bit less flexible, but perfectly adequate for your needs. And since you're sorting the active sheet, you can skip sheet references altogether! Try this:

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

[micina]

Ah!! This worked!! Thank you so much, both of you!

[leelnich]

You're most welcome! If complete, please mark your thread as SOLVED (Thread Tools up top). Thanks -Lee

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

[mehmetcik]

Hey Guys

Posts One and Seven do not have Code Tags.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)