Apply macro to active worksheet

  • Hi folks,


    I'm setting up a spreadsheet for a local charity run kids club and would like to include a macro that will arrange the names A to Z by surname. I can get the macro working on a specific page, but when I add a new page the macro wont work. I have attempted to edit the code from specific sheet names to 'ActiveSheet' but this is incorrect. Can anyone help?


    Here is the code for my macro:-


  • Are you saying that you want to sort a list of names on more than one worksheet? If you attach a copy of your file, it would be easier to test a possible solution. De-sensitize the data if necessary.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Your post does not comply with our Forum RULES. Use code tags around code.


    Posting code between

    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.


    (I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

  • Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro below into the empty code window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. Run the macro after each time you add a new worksheet.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • I have one other macro that I would like to set up. I would like it to make a copy of the last worksheet and either automatically rename it, or give me the option to rename it, then I'd like it to copy the 'current balance' column from the last worksheet and paste that data into the 'previous balance' column on the new worksheet. I don't mean to push my luck, but if you could help with that at all we'd be very grateful :)

  • **Update**


    I have managed to add the macro to copy the active page and rename it, and I have assigned that to a button, however, when I try to set up a macro which copies the 'current balance' column from the source sheet and paste it to the 'previous balance' column of the new sheet, I encounter the same problem I was having with sorting the surnames alphabetically, in that it copies the figures from a specific sheet, rather than from the last worksheet. I have uploaded my worksheet again with the working macros. Can you possibly help ? I'm trying to make the spreadsheet as user friendly as possible, as some of the voluntary staff at the kids club are not confident with computers at all


    Code
    [ATTACH]n1202496[/ATTACH]
  • Try the attached file. I've added two buttons. I assumed that when you copy the sheet, you want the data in columns C to M and O of the new sheet to be cleared, ready for new input for that week. If this is not the case, just delete the two lines of code with "ClearContents" in the "CopySheet" macro.

  • You are very welcome. :) My best to all the kids (from a retired teacher and administrator).

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!