Can a code work in only ONE worksheet?

[DoctorMorty]

Hello, I have the following code in Sheet1 that is supposed to auto-update the timestamp in cell "J36" when anything has been updated in the sheet(1).

Please Login or Register  to view this content.

Also, I have the same code in Sheet3, again, to do the same - auto-update the timestamp in cell "H36" when anything on that particular sheet(3) has been updated

Please Login or Register  to view this content.

The codes work, however, if I update Sheet1, then the timestamp in both Sheet1 and Sheet3 auto-updates, and same for when I edit Sheet3 - timestamp updates on both sheets. My question is - how can I get the codes to only work for the sheet that they are on? For example, when I update Sheet1, I just want the timestamp on Sheet1 to update, not on both.

Thank you.

[JosephP]

there is nothing in those codes that would affect any sheet other than the one containing each. something must be triggering a change on the other sheet

[DoctorMorty]

I do have a code on Sheet2 to update multiple pivot tables all at once by clicking on a button.

Please Login or Register  to view this content.

Would this have anything to do with it? Otherwise, this is it, 3 sheets, total, and that's all the macros in the workbook.

Thank you.

[JosephP]

if you run that and there are pivots on each sheet then both change events will be triggered

[DoctorMorty]

I tried taking out the pivot table code to no avail, timestamp still updated on both Sheet1 and Sheet3. And no, the pivot tables are only on Sheet2 (it's grabbing info from Sheet1). Soooo....

Sheet1 = autoupdate timestamp macro
Sheet2 = pivot table macro
Sheet3 = autoupdate timestamp macro

Let me know if you need more info, otherwise I'm a beginner and thought I was keeping it simple enough for my first time with macros.

[JosephP]

I'd need to see the workbook-the posted code does not explain that behavior (unless you have both sheets selected!)

[DoctorMorty]

Oh man, I was hoping you wouldn't ask to see it, lol, it has confidential info on it =/

Okay, I'm hoping this will help you [help me], as I cleared the contents, but left the formatting and codes, and whatnot.

[JosephP]

I'm afraid I am not seeing the behavior you describe-if I change a value on one sheet only that sheet's date is updated

[DoctorMorty]

That's the other thing I was afraid you'd say. However, I did notice one thing yesterday after taking off and putting back on the pivot table code, the timestamp on Sheet3 now autoupdates upon opening and closing of the workbook.

[JosephP]

do your pivot tables update automatically when the workbook is opened?

[mehmetcik]

Hi

Sorry to intrude.

I sometimes declare a Public changeflag and use that to ensure my code only runs when I expect it to run.

Could you not do something similar.

in your scenario I would use something like:

Please Login or Register  to view this content.

[DoctorMorty]

Negative; I created the button so that all pivot tables are updated at once as opposed to "refreshing" each table one-by-one. But no, I did not see that the tables were being updated automatically, just the timestamp on Sheet3 upon opening and closing the workbook. It seems we have taken a step back.

[DoctorMorty]

No apologies, all input is welcomed.

What does the code do and where should I put it?

[JosephP]

did you remove any controls from any sheets?

[DoctorMorty]

did you remove any controls from any sheets?

Since I copied and pasted those codes into my sheets (only modifications I did was the password on the pivot table code and the targeted cells in the timestamp codes), I'm 100% sure I didn't do anything else, as I'm just a beginner in Excel

[JosephP]

do you see the same behavior with the sample file you posted?

[DoctorMorty]

do you see the same behavior with the sample file you posted?

Okay, this is very, very strange. In the same file I attached, I was able to input/edit Sheet1 and the timestamp changed in just the Sheet1 and did not update Sheet3. I waited a few minutes to update Sheet3, and when I did, the timestamp updated in just that sheet alone, leaving Sheet1 alone.

Other than clearing the contents in the entire workbook, I am not sure why it would be working the way I would like for it to work.

[JosephP]

are you sure you didn't have multiple sheets selected? (you would see [group] in the title bar)

[DoctorMorty]

are you sure you didn't have multiple sheets selected? (you would see [group] in the title bar)

Pretty sure, Joseph, pretty sure.

[JosephP]

does the original still exhibit the problem?

[DoctorMorty]

does the original still exhibit the problem?

Hm, it works just fine. I don't know what to say, Joseph, been researching for a week, only when I finally asked the question, it works.

[JosephP]

could be excel gremlins-does happen now and then

[DoctorMorty]

Thanks Joseph, for your help!

[JosephP]

you're welcome :-)

if the problem starts again let us know

[mehmetcik]

Hi

This code would go at the top of each macro.
To check which sheet is calling the macro. and if it isn't the right one it jumps to row 100 and exits.

Please Login or Register  to view this content.

This code goes at the bottom of your macro just above "End Sub"

Please Login or Register  to view this content.

[DoctorMorty]

Joseph/mehmetcik/everyone

I think I figured it out...

It's not so much the codes, themselves, it was so much the order in which I was putting the codes in the entire workbook.

In another copy of the workbook, with no codes, I edited it by inputting the pivot table code first, then the auto timestamp codes next (first in Sheet1 then Sheet3). Both timestamps were autoupdating upon opening and closing of the workbook, which clearly was not the goal. SOOO...

I deleted all the codes and the "=NOW()" function in the sheets, saved the workbook, then one by one, inputted the auto update timestamp code, THEN the "=Now()" function in Sheet1, saved it, tested it, and it worked just fine. Then, again, inputted the code for Sheet3, inputted the "=NOW()" function, saved it, tested it, and boom - worked, and without autoupdating the timestamp in Sheet1. So then finally, inputted the code to update the pivot tables, saved it, tested it, and boom boom boom, the entire workbook is working to the way I wanted it to work.

So again, thank you both for your inputs! I think maybe this is something to consider when working with different codes in a single workbook in the future, who knows?!

Anyhoot, thanks again!

[JosephP]

this is the first time you mentioned an =now() formula-that is volatile and will update whenever the workbook calculates for any reason as well as when it opens (the code does not use a formula). it sounds as though that is the cause of your behavior

[DoctorMorty]

Interesting. I checked the workbook just now, and the timestamps on both sheets did not update to the current time, which is good. Which means I'll just leave it, for now, and if I'm having issues, I know where to look. Thanks Joseph.

[JosephP]

if you put the codes in the sheet before entering the =now() formula then the code will overwrite the formula with a static timestamp immediately after you enter it. if you already had the formula in place before entering the code then the code would not overwrite it until you entered some other data to trigger the change event but the formula would update itself automatically with each calculation until then