Auto Update VBA Project (Macro?) using "Worksheet Name"?

[Pooger]

HELP! I have created a "reset/clear button" on my worksheet (using the code shown below). I need to create multiple copies of this worksheet and change the name of the worksheet (tab) to something different (i.e. an individual employee's specific name). Is there an easy way for me to write this code, so that "Salaried Timesheet" automatically updates to the new worksheet "name", without having to change the code for each worksheet I need to create??

Please Login or Register  to view this content.

I didn't attach my actual workbook, because it would take quite a bit of work to provide a "sample". If you need my workbook to assist with this issue, please let me know. Any suggestions you could offer would be most appreciated.

Please note - I am VERY much a novice when it comes to VBA/Macros, so my apologies if this is silly question (or if my "Title" made no sense - wasn't quite sure how to word it based on my question).

Thanks!!

[TMS]

I'd do it something like this, assuming that you have the same layout on both sheets:

Please Login or Register  to view this content.

Note that I have also re-ordered the cell clearing so that it is more easy to see which cells/ranges are being cleared.

Regards, TMS

[TMS]

Note that you should use Code Tags in order to comply with forum rules. Please take a few moments to read the rules.


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

[TMS]

Assuming that the button to clear a sheet exists on the actual worksheet it relates to, you could perhaps use something like this:

Please Login or Register  to view this content.

Regards, TMS

[Pooger]

My apologies that I did not post this properly. I'm not sure that I am fully understanding how I should have posted my question, but I will read through the rules more thoroughly.

I appreciate your sharing your solution. Unfortunately, I'm still a bit confused. I actually need to create many different copies of this worksheet (and rename the worksheet for each copy I make). As an example -- my current worksheet is named "Salaried Timesheet", I will need to rename that worksheet to, let's say, "Sample, John" or "John Sample".

So it's more than one (or 2) worksheets. Ideally, I would like to simply change the name of the worksheet "tab" and have my code recognize that automatically. All of the worksheets will be set up the same, so the ranges are okay. However, when I change the name of my worksheet "tab", the code no longer works. In the solution you provided, does this do this and if so, I don't understand "Salaried Timesheet 2".

I'm so sorry, I am very ignorant when it comes to this coding stuff.

Thank you.

[Pooger]

the button does appear on the actual worksheet - but I don't want to clear "everything" on the worksheet, only the ranges that appear in my original question....

[Pooger]

I'm just testing my original code to determine if this is how I should have included it in my question...

Please Login or Register  to view this content.

[TMS]

Yep, that's the way you need to post the code. Thanks. Now please edit the original post to also include Code Tags

I'll upload a sample workbook tomorrow.

Regards, TMS

[Pooger]

Thank you so much - I very much appreciate your help!

[TMS]

OK, see the example attached. It contains a Template worksheet with some sample data. Right click on the Template and make a copy. When you copy the template, it copies the whole worksheet including the button AND the code behind the worksheet.

If you click on the button on a COPY of the template it will inform you which sheet's cells will be cleared and asking for confirmation. If you select "yes", it will clear the cells; if you select "no", it will not.

If you click on the button on the Template worksheet, nothing happens. This is to protect the data on the template for testing purposes.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards, TMS

[Pooger]

Thank you Trevor, however, I'm not sure this solves my issue. The worksheet copies will be renamed and saved as separate, individual files - not separate worksheets within a single workbook.

With that said, is there a way for the "code" to automatically recognize or refer to the new "worksheet name" when it is changed?

So, in the example below --- the worksheet name is "Salaried Timesheet". If I were to change the "worksheet name" to "Hourly Timesheet", can something be added to the code that will recognize that I've renamed the worksheet and update the code to recognize the new worksheet name automatically so that the clear/reset button will work in the newly named worksheet? Meaning in the code below, where it says "Salaried Timesheet", it would automatically change to the "[new worksheet name]" regardless of what I named the new worksheet? I'm so sorry, I'm not getting this.

Please Login or Register  to view this content.

[Pooger]

I think I may have figured out a solution to my problem! The following code seems to work regardless of what I "name" the worksheet!

Please Login or Register  to view this content.

[TMS]

Ah, good, yes that would work. Obviously, thinking too hard

I'd still put the code inside a With ... End With, but your choice (and I'd re-order the cell clearance, but, again, your choice.

Regards, TMS

[Pooger]

Ah, good, yes that would work. Obviously, thinking too hard

I'd still put the code inside a With ... End With, but your choice (and I'd re-order the cell clearance, but, again, your choice.

Regards, TMS

Thank you Trevor! Just out of curiosity, what does the "With ... End" actually do....and what does "re-order the cell clearance" mean?

[TMS]

Please Login or Register  to view this content.

With ... End With creates an internal reference to the object so it does not need to work it out several/many times.

By re-ordering the lines of code, I just mean sorted ascending alphabetically so it is easier to determine which cells are being cleared.

Regards, TMS

[Pooger]

Ahhh, that makes sense. Thank you so much!

[TMS]

You're welcome. Thanks for the rep.

[Pooger]

Hi Trevor - I can't seem to get your code to work. When the "button" is clicked, nothing happens. I've actually provided a sample worksheet, as it may be easier to see what I have going on here! Note: I have turned off worksheet and workbook protection for the purposes of sharing. For some reason (that I can't figure out), it seems to take about 10 seconds for the workbook to actually open (which may be because of how the current code is written). Would love to try and get your solution to work if it will help to reduce the "opening time".

Additionally - I'd like to somehow do the following:

1. Change the "worksheet" tab name to mirror the contents of cell C4 (the person's name) - currently named "Salaried Timesheet"

2. There is another worksheet in this workbook named "Off-Week Timesheet". When this Tab is opened, I'd like to display a "pop-up" message that says something like "Warning! Only use this Timesheet for previous or future weeks" (haven't totally figured out my exact wording yet (so I'll need to be able to edit).

Can you provide any assistance? Or should I post this as a new thread?

Sorry and Thank You for your patience with me!!