+ Reply to Thread
Results 1 to 18 of 18

Macro To Sort Columns Only Works on One Tab

  1. #1
    Registered User
    Join Date
    09-28-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    36

    Macro To Sort Columns Only Works on One Tab

    I am making my first entry into the world of Excel macros today. I need to sort two columns (say, A and B) in descending order by col B. I have three workbooks each with many individual tabs and each tab is a spreadsheet with many such paired columns that will need sorting. All tabs on all workbooks are exactly the same shape (2col x 32row). This will involve a huge amount of repetitive action, so I thought I'd try a macro.

    I followed some online instructions and was able to record and use a macro I stored in my personal workbook. The problem is that while the macro worked perfectly on one tab (I was so proud of myself!), it did not work at all on other tabs in the same workbook. Any ideas? Thanks much!
    Last edited by KimC; 09-13-2014 at 10:51 PM.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro Question

    You will need to modify your title. Something Like "Macro to sort every worksheet in a workbook".

    If you fail to do so both you and anyone else replying to your post will be penalised by a moderator.

    When you recorded your macro you probably selected sheet1, so whenever you run the macro it sorts sheet1.

    Try this instead.

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 09-13-2014 at 09:38 PM.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Macro Question

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Registered User
    Join Date
    09-28-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro To Sort Columns Only Works on One Tab

    Thanks for the head's up about the thread name. I have changed it, I think. Thanks also for the macro suggestion. I tried it and still ran into problems (it put some garbage in the upper left corner of the spreadsheet), but after my original post I did figure out that, as you suggested, I had locked it into the first spreadsheet. I'm still trying to make it work by using the information you sent.

  5. #5
    Registered User
    Join Date
    09-28-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro To Sort Columns Only Works on One Tab

    Now I've really messed it all up. Looks like I saved over my personal workbook with one of the files I was working on. I don't know what happened....I was trying to record a macro and save it in my personal workbook, but now it's treating the filename (which is now blank) like it was the personal workbook. When i tried to record a macro anyway, it gave me an error and said the personal workbook must be in the startup folder before I can do a macro. Boy am I confused.

  6. #6
    Registered User
    Join Date
    09-28-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro To Sort Columns Only Works on One Tab

    Now I've really messed it all up. Looks like I saved over my personal workbook with one of the files I was working on. I don't know what happened....I was trying to record a macro and save it in my personal workbook, but now it's treating the filename (which is now blank) like it was the personal workbook. When i tried to record a macro anyway, it gave me an error and said the personal workbook must be in the startup folder before I can do a macro. Boy am I confused.

  7. #7
    Registered User
    Join Date
    09-28-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro To Sort Columns Only Works on One Tab

    Things are getting worse...I don't know what I've done but now whenever I open a new, blank spreadsheet in Excel, the default title at the top is "PERSONAL.XLSB" instead of "BOOK1.XLS". This is very worrisome. How can I fix it? Thanks!
    Last edited by KimC; 09-14-2014 at 01:19 AM.

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Macro To Sort Columns Only Works on One Tab

    Personal.xlsb will always open when you launch Excel. If you don't wish to see it, then you must set the visibility for the sheet to hidden. In order for the saved "personal" macros to operate the file must be open, but it may be hidden. Once it is hidden and you open a new workbook, it should then open as Book1.xlsx, etc.

    For further info on Personal workbook, look at this link

    http://www.rondebruin.nl/win/personal.htm

  9. #9
    Registered User
    Join Date
    09-28-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro To Sort Columns Only Works on One Tab

    Well I guess the entire Forum is having a good laugh at that one! Thanks for your answer. I'm sorry I panicked, but it was late and I thought I had really broken something when i kept seeing the changes that I had apparently made and couldn't figure out how to undo. You're embarrassingly right...I hid the PERSONAL.XLSB and now Excel opens with Book1.xls. The good news is, I didn't even know about that personal file 24 hrs ago, so I've learned a lot about what it is, where it is, and what it does. The bad news is, I'm back to my original problem of figuring out macros!

  10. #10
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro To Sort Columns Only Works on One Tab

    Hey Kim,

    We all freak out from time to time
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Macro To Sort Columns Only Works on One Tab

    We all freak out from time to time
    Especially when under a little bit of pressure

  12. #12
    Registered User
    Join Date
    09-28-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro To Sort Columns Only Works on One Tab

    First let me say I noticed there is a part of the forum devoted to macros, so this thread is probably in the wrong place. If it's possible and appropriate to move it to the Macros section, please do or tell me how.

    Meanwhile, I still have my original problem where a macro I created (my first) is working fine on the first sheet of a multisheet file, but I can't get it to work on any other sheets in the same or any other file, even after I saved it on PERSONAL.XLSB. The first suggestion in this thread for the macro program is apparently correct that I have the macro set to work on only the first tab, but when I tried that suggested programming, it made data appear in weird places. I have created a cut down version of my file with simple, fake data so you can see more specifically what I'm trying to do. I hope the macro comes across (I had saved it to my workbook for the attached file). I want to have a macro that sorts the cols B and C based on col C (largest to smallest, although I'll need the reverse for other files). The columns will change, but the pattern is the same on multiple tabs and files.

    In case the macro doesn't show up with the spreadsheet, here it is:

    Please Login or Register  to view this content.

    I hope this post is no inappropriate. I've fiddled with this for 3 days and looked up many macro tutorials online, but I haven't got this working yet, so I need some other opinions. Thanks everyone!
    Last edited by KimC; 09-19-2014 at 12:25 AM.

  13. #13
    Registered User
    Join Date
    09-28-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro To Sort Columns Only Works on One Tab

    Had trouble attaching the attachment! Here it is.TestFile-MacroIssue.xlsm

  14. #14
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro To Sort Columns Only Works on One Tab

    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.

    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

    Take care of the code tags and, if needed, I'll look at this tomorrow

  15. #15
    Registered User
    Join Date
    09-28-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro To Sort Columns Only Works on One Tab

    Thanks for the instructions. I fixed it (I think).

  16. #16
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro To Sort Columns Only Works on One Tab

    Yeah - you fixed it!

  17. #17
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro To Sort Columns Only Works on One Tab

    Hey Kim - I don't understand why you only want to sort columns A & B - please clarify

  18. #18
    Registered User
    Join Date
    09-28-2013
    Location
    San Antonio, Texas
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro To Sort Columns Only Works on One Tab

    I may have written that wrong in my previous post. I want to sort columns B and C, then the ranking in col A will fall into place, so I'm not sorting on col A. My apologies for the confusion. I copy and paste from another source into columns B and C, so the formula in col A ranks them. In the sort dialogue box, I sort first on col C (largest to smallest) and secondly on col B (A to Z). Then, I want to move over and do the same sort the same way with columns E & F and H & I, with the primary sorting on cols F and I. And, I need to do the same sort on other tabs in the file, and even on other files. In other words, I have to do this sort dozens of times on pairs of columns, all the same length, so I thought a macro would be a big time saver if I can learn how to set it up. Hope that clarifies some, but please let me know if you have more questions. Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dumb question about macro Over macro
    By david gonzalez in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-03-2014, 03:48 PM
  2. Excel Macro Question Box - goto different parts of the Macro
    By krishatherley in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-19-2009, 09:12 AM
  3. Replies: 12
    Last Post: 05-24-2007, 02:18 PM
  4. macro question and count question... thanks!
    By bstaff in forum Excel General
    Replies: 5
    Last Post: 02-07-2007, 10:12 AM
  5. a macro question
    By Jymoz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-30-2006, 11:03 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1