I want to restrict my macro to a specific worksheet.

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hello, I have a workbook with two macros. The first macro (Daily_Report) runs a report based on the data in the second tab Report Data Collect) of the worksheet and provides the report in the third tab (Report). The second macro (Undo) is to Undo the actions of the first macro. I want to be able to restrict the Undo macro to only run on the third tab (Report). I know nothing about writing code, so please, if you have a solution, it will help me tremendously if given step by step instructions. I thank you in advance for any help and greatly appreciate your taking the time to assist.


    Hopefully, I've followed the rules with this post, and I apologize if I've not done so. Thank you.


    Code
    [attach=1227899][/attach]

    I can't open your workbook.


    I'm not sure why you seem to have tried to put an attachment into the code box.


    You can try something like this


    Code
    Sub MyMacro()


If ActiveSheet.Name <> "sheet to restrict to" Then Exit Sub
''///your code here
End Sub

    My apologies. I'm not quite sure why I tried to put the attachment there either....other than I'm new to your site and haven't quite figured it out yet. I have attached an unrestricted file.


    Try this


    I've tidied up the code a bit. Give these macros a try;

    You can say "THANK YOU" for help received by clicking the "Like" 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.

    Hi,


    I have tried the code royUK has provided, but when I run it, I get a run time error '1004'.


    I have also tried the code Mumps has provided, but all that happens when I run it, is a new worksheet opens with nothing on the report page.


    I am just copying the code provided and pasting into my macro. I'm not sure what I'm doing wrong.

    I tried it on the file you posted and it worked properly. Replace your old macros with the posted ones.

    You can say "THANK YOU" for help received by clicking the "Like" 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.

    Love 1

    Nevermind....it works. I copy and pasted into a copy of the file I sent you so in case something went wrong, I wouldn't lose my original. It didn't like a different file. Once I copied the code into my original file, it worked like a charm.


    Thank you both so very much! You've no idea how much this helps me!


    Have a great day!

    You are very welcome. Glad we could help. :)

    You can say "THANK YOU" for help received by clicking the "Like" 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've re-written the code. Should the report macro be filtering before copying?

    Hi Roy, I'm all set, thank you. It works...I was replacing the code in a secondary file which I used in case something went wrong, I would still have my original file, Once I used the original, it works great!


    Thanks to both of you who helped me on this...I really appreciate it!

    Actually, now that I've had a few minutes to think about it, I have a question about using the macros in a different file. We will have a new file for 2021, at which time the filename will change, but I'll still need the macros to function. Can you tell me what I'll need to change in order for it to work on next year's file?

    Pleased we could help.


    I have completely re-written the code for my last example so that your code is working with dynamic ranges instead of hard coded ranges.

    Try these modified macros. If the sheet names and ranges in the 2021 files are the same as your current file, the macros will work without any changes.

    You can say "THANK YOU" for help received by clicking the "Like" 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.

    Hi Mumps. I tried your modified code, but it doesn't appear to be working the way I expected it too. First, I replaced the code in my original file, saved the file using Save As and renamed it for 2021. When I ran the Daily_Report macro it opens my 2020 file and runs there. I then tried going to my original file, then saved the file using Save As and named it for 2021. The macro still wants to open my 2020 file. Is there anything I can do to combat this? These are annual log sheets, so I will want to create a new log for each year, 2021,2022, etc., but not have to recreate macros each year.

    Hi Roy. I wasn't clear about your last message. I understand you rewrote the code....but I don't see the code...am I to go back to your original response? Or the later response where you attached the file? Either way, the macro wants to open my original 2020 Active Log Book 20200422 file.

    I must not understand what to do. I've downloaded your file, and when I run the Daily_Report macro, Excel wants to open my original file and run the macro there. I then replaced the code in my original file with the code from your file, saved it and it works fine. I then resaved that file with a new filename and it wants to open my original file. Perhaps there is a setting somewhere in my Excel that needs to change? Or perhaps a setting within the macro itself? Is it because my original file still exists?

Participate now!

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

Register Yourself Login