+ Reply to Thread
Results 1 to 8 of 8

Sort function using a macro

  1. #1
    Registered User
    Join Date
    09-24-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    4

    Sort function using a macro

    Hi,

    I am a bit new with macros, and I was hoping you guys can help me with my problem so I joined in.

    Here it is:

    I have a payroll sheet with several columns. The rows are divided into two parts, for office employees and for sales personnel. (Please see attached file.)

    More often, there will always be new employees added to the roster. I've recorded a macro that adds new rows for new employees and automatically copies the formulas from the rows above. After the new rows have been inserted, the basic information for the new employee have to be filled in (name, salary rate, etc.). What I wanted to do was to automatically sort the table using the last names of the employees after I've filled in the necessary info for the newly-added ones. I recorded a macro that sorts (for example) all office employees (using last names) from A-Z. Problem is, after I've added the new employee and run the macro, it only sorts up to the row that I've originally selected when I recorded the macro. Basically, my problem here is on the rage. I think the macro fixed the range. I've read one post here about "Using Sort function in macro crashes". It's a bit similar to my problem. I can opt to use the long range you suggested there, but I am hampered by the fact that the rows are categorized into two and I figured the long range wouldn't do for the office employees category (please see attached file).

    Simply put it, I just want that after I've added new rows for new employees and filled in the necessary info, I can run a macro that sorts out the employees by their last names from A-Z.

    Hope you can help me out. Thanks!
    Attached Files Attached Files
    Last edited by cede; 09-24-2012 at 09:59 PM.

  2. #2
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    Re: Sort function using a macro

    Hello!
    I have taken a look at your sheet.
    Try this out and tell me how it works for you.
    Make a button on the worksheet and asign this macro to it:
    Code:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-24-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sort function using a macro

    Hi!

    Thanks so much, it worked for me!

    But one last question, if I changed the heading (e.g. the "Office" and "Sales" above the list of employees), which parts of the code do I need to change too, so that it'll still run? I tried changing "Sales" to "Collection" and it prompted an error. Teehee.

    Thanks again!

  4. #4
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    Re: Sort function using a macro

    Yes if you change the Sales to Collection you have to change the part of the code that finds the Sales word so this part:
    Please Login or Register  to view this content.
    The red part of the code. So just the word

  5. #5
    Registered User
    Join Date
    09-24-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sort function using a macro

    Got it. Thanks so much!

    Would you mind if I ask yet another question? What if I now have three categories (for ex: Office, Sales and Collection) all in the same sheet? I tried tweaking the code you had given and just plain copied the second part of your code as the code for the new category, then I changed " 'Sort Sales " to " 'Sort Collection " (for the new category) and the "Find("Sales:"..." to "Find("Collection:"..." . It ran but it prompted the error message "Duplicate declaration in current scope". I don't really know about these codes and stuff, but I figured it was because of "Dim r as Range". "r" has already been used twice. Am I following the logic of this whole code stuff? Teehee. But anyway, I tried changing "r" to "r1". I assumed all the "r"s will refer to the range so i changed all of it to "r1" for the third category. No luck. This was prompted : "Object variable not set", and "uRows1 = r.Offset(1, 0).End(xlDown).Row" under 'Sort Sales was higlighted yellow.

    Hope I can have one last help from this one. Teehee.

    Thanks so much!

  6. #6
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    Re: Sort function using a macro

    Then try this if you have 3 fileds you want to sort
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    09-24-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Sort function using a macro

    Problem triple-solved! Thanks a lot!

  8. #8
    Forum Contributor stojko89's Avatar
    Join Date
    05-18-2009
    Location
    Maribor, Slovenia
    MS-Off Ver
    MS Office 365
    Posts
    913

    Re: Sort function using a macro

    No problem
    And thanks for marking the thread as solved! And for the Rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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