macro runs on sheet that starts it, not top sheet.

i have been using:

Please Login or Register  to view this content.

to run some macros at times specified in F40 and F41 on several sheets in my workbook. small problem is it only seems to run macrojamie and macrojamie2 on the sheet that is open on top. how can i get the two macros to apply to the sheets that cause them to run?

macrojamie:

Please Login or Register  to view this content.

macrojamie2:

Please Login or Register  to view this content.

Hello Jamie,

Your macros Jamie1 and Jamie2 operate on the range of the ActiveSheet. Your loop doesn't activate each worksheet as it steps through them.

Example of Activating each worksheet

Please Login or Register  to view this content.

Sincerely,
Leith Ross

leith,

so

Please Login or Register  to view this content.

will make the sheet that triggered the macro the active one, is that right? or have i grasped what you have done wrongly again?

Thanks for the help

sorry leith, or anyone, just another question. i am finding it useful to know why so i do not keep asking the same things!

what is the 'application screen updating' for?

sorry to be a pain leith, i just tried out your code and deliberately clicked on the tab so a sheet that does not activate jamie1 or jamie2 is on top. when the time in the cell on one of the sheets underneath fires one of the macros, they still only copy and paste the top sheet and not the one causing them to play, if that makes sense.

I have all the code in just modules and the fist bit of code is attatched to a button, so when all my desired sheets are open, i hit the button and it starts. i really just want jamie1 and jamie2 to run on the sheet that activates them and it is really frustrating me now as the sheet that activates them varies at different times. As a workaround, is it possible to make the sheet appear on top as the jamie1 or 2 run, or is there a much simpler way, as there always seems to be!

Hello Jamie,

To answer your first question, "What does Application.ScreenUpdating for?" This property controls whether Excel updates the screen while a macro is running. Setting this to false will prevent you from seeing the data be written onto the sheet, and the sheets being switched as the macro runs.

For the second question, I think it would be best if you post your workbook for review.

Sincerely,
Leith Ross

thanks leith, this is just about the only thing holding me back finishing this project now and i have learnt a lot doing it. Any help would be appreciated. the sheet called '13h04 BSTA2 480M' on this example should trigger the 2 macros, which it does if it is the sheet on top. if another of the sheets are open over it, '13h04 BSTA2 480M' will still run the macros, but they will do what they are supposed to on the top sheet.

If i can get this cracked, then i can work on tidying it up and improving it.

regards, Jamie

Hello Jamie,

This code in Module 3 iis the only code I found to run the macros Jamie, and Jamie2.

Please Login or Register  to view this content.

This sets up the each worksheet in the workbook to run these macros based on the formulae you have in cells F36 to F41. You won't see the sheet when its time comes because the Application.ScreenUpdating is turned off. ANother potential problem is arises from time conflicts. If two or more sheets have the set their times set the same, you won't see each sheet when the time comes to run the macro.

Sincerely,
Leith Ross

ok, bear with me. i did not write a lot of the code on the sheet, just added to it so my understanding is limited but getting there!

So the application.screenupdating=false basically will still let the sheet become active, but it will stop it visually becoming active to save on the top sheet switching every few minutes and driving me nuts -is that right?

i have tried removing this line and now when the sheet becomes active, it will come to the top and the cut/paste macro will work. oddly though, the sheet that becomes active is the "market - master" sheet whenever a sheet triggers macrojamie1/2. i think this is because market - master 'creates' the sheet when it is opened ie, i open a new sheet and it is called "market - master 2" before the workbook renames it a split second later as its correct name. i think it somehow is attatched to market master and therefore if it becomes active, the market master is the one the workbook thinks is the active one.

oh well, back to the drawing board, unless anyone can offer some help?

from what i can work out, this code creates a copy of market-master, but renames it. i think that the renamed copies, if changed to the active sheet render market-master as the active one still. can anyone confirm if i am right? if so any ideas? as the code looks a bit beyond me to change (at the moment!), i was thinking along the lines of creating duplicates of all of the market master renamed sheets, but adding jp to the end of the name and then trying to make those the active sheets, hoping that they will be recognised as being their own sheets and not as copies of market -master. not the most elegant way and will leave me with a lot of open sheets, but hopefully within my realm of understanding. if anyone can offer some advice, other than give up, i would be grateful!

Please Login or Register  to view this content.

ok, i have tried to change the code above as follows:

Please Login or Register  to view this content.

What i was trying to do was to get it to open a new sheet, as it did before, but not to create it from the market master sheet, but create it and name it the market name straight away, then paste the desired formulae (F9:EJ5057) from market master onto the new sheet, avoiding the need to start the sheet as market master and rename. as i am an entire novice and am just playing and changing things to see what happens, it obviously does not work.

if you could offer any advice or help again, i would be grateful as i dont know where to take it from here and have hit a brick wall!!

jamie

Hello Jamie,

I agree about the copying the sheet as a potential problem because any likeed formulae will be copied as well. If these new sheets don't require any calculation to be made then copying the values and pasting only the values should prevent any further problems.

Sincerely,
Leith Ross

thanks leith. just when i am starting to get a small grip on this VBA, it confuses me again. With the above, how would i change it to just start a new sheet with the market name as it does, but not paste the formulae from market master. i have been playiing with the code and the best i can get is it to open the querytable on top of the active sheet, resulting in a big mess!

*without it being affiliated to market master, so i can set it as an active sheet and solve the original problem

Hello Jamie,

Do you really need the external data Query for the Market Master sheet or can you obtain the information from a file? I am trying to follow the sequence of operations in what you are doing. At this point, I am lost.

Sincerely,
Leith Ross

any new market that is opened needs the external data query that i believe is set to update at 30 second intervals. i want the new sheets to update every 30 secs and then use the loop i posted at the beginning of this thread to render the sheet as the active one so that macrojamie 1 and 2 can work on it.

basically, i want the new sheets opened to be called the market name and at 15mins before the time quoted at the beginning of the market name to run macrojamie on that sheet only, and then 2mins before the time at the beginning of the market name to run macrojamie2.

Could there be an easier way than what i am doing then, as i am only really working to the stuff i vaguely understand.

Hello Jamie,

Thanks, that explanation helps clear things up for me. I'll work on the code and post back with the modifications. You can try it and see it works correctly.

Sincerely,
Leith Ross

i appreciate it!

Hi Leith, or anyone else that could help. been trying for a while now with no joy. during the course of my trials i have set the screen updating to true. when i do this, it cycles through the sheets only once. should it not continuously cycle through the sheets?

i still cannot get it to run either macrojamie on only the sheet that causes it to run.

Leith, just to let you know that my wife has seen me posting on here and decided that leith has been earmarked for the name of our next kid (if we have any more!)

jamie

ok, this is only what i am teaching myself,but i canget it to continuously run through the sheets ok with:

Please Login or Register  to view this content.

but it seems to make the processor go mental and crash excel after 20 seconds. any help out there please?

Hello Jamie,

I would be honored if you name your next child Leith. It was my maternal grandfather's name. In the U.S. there are fewer than 4000 people with the name Leith either as a first name or family name.

This is going to be a crazy week for me. My daughter is registering for college and her car is in the shop. Her mother is out of town, so that leaves me to get my daughter to college and to and from work. In between all this, I am trying to keep up with previous and new posts. Your patience is greatly appreciated.

Sincerely,
Leith Ross

Thanks Leith, Got to have it first though! Things are getting a bit tight in the UK as 90% of the population have gone mad with credit and are now having to pay it back. Learned (the hard way!) when i was a bit younger to be careful, so have almost no debt and even i am feeling the pinch. May have to wait a year or so until things calm down with everyones money, but you will be the 1st to know when/if it happens.

I can be as patient as you need, after all you are doing me a favour. I will carry on blindly playing around and let you know if i crack it. Last night i thought about ways to get the code that cycles through the sheets once only loop through them without blowing up my PC. after looking around on here and google, i was wondering whether i could use the application.ontime command to just run it every 20 seconds or so, but i am not sure if this will work as the code is unlikely to run as the clock changes and i guess that if it runs and it is the correct time plus a few seconds it may not work, so i will experiment.
Not easy to get the ontime method to work at the moment as it doesnt seem to like my code. Anyhow, i am learning a lot and wait with interest to find out the correct way of doing it when your life returns to normal!!

Regards, Jamie