'snapshot' of cells values into other cells IF F50=1

[jamiepullen]

hi, is there any way i can take a 'snapshot' of a range of cell values and copy them into a specified range on the same sheet if F50=1? I dont want to just copy them as the cells update and change every minute and i just want the values when F50=1. I would like the same to apply to each open sheet on the workbook, as they all have F50 as a 'trigger' and the cells to be copied are the same range

regards, jamie

[Richard Buttrey]

How about using a Worksheet Selection_Change event using F50 as the target cell, and then code like

Please Login or Register  to view this content.

HTH

[jamiepullen]

will have a go when i get home, thanks.

Will that work for all open sheets?


Regards, jamie

[Richard Buttrey]

Hello Jamie,

Not unless you put the same code in each of the Worksheets.

Depending on how your process works you may be able to write a macro to cycle through all the sheets and test F50 against another check cell to detect a changed value, but that of course will mean that the code that updates F50, first needs to copy the F50 and paste it as a value in the check cell.

HTH

[jamiepullen]

the situation is that i have a sheet that i type formulae in and it is put into the same cells on all other open worksheets and any new ones opened except 2 of them. I have F50=1 if certain criteria are met on the sheet it is on. F50 is updated using 'IF' formulae, as i have no vba knowledge.


I was hoping i could take a snapshot of the cells (say A1:J1) and transfer them into different cells on the same sheet (say K1:T1) when F50=1. I was hoping i could get it to apply to all sheets, even new ones, as when i open a new sheet, the code someone very kindly wrote for me on here transfers all the formulae across to itself.

As each sheet has slightly different data, F50=1 at different times on different sheets. i hope this makes sense.

Regards, Jamie

[Richard Buttrey]

Hi Jamie,

It sounds as though F50 is not actually the trigger, rather the cells that cause F50 to evaluate/change. What event is causing F50 to change? You suggested earlier that the workbook is being updated every minute. Is that an automatic feed from a web site?

It might be better if you attached the workbook so we can see your requirement in its context. It's always easier that way.

Rgds

[jamiepullen]

see what i can do, by the way, it is I62 that is the trigger, not F50.

[jamiepullen]

it is from a modified sheet given to me by a friend who knew i was trying to learn excel. I had no interest in the subject, but the sheet was amazing and really showed me what excel can do. He was using it for horse racing to actually place bets automatically. since then, i have been playing around with the sheet partly for my own amusement and partly because he was interested to see is i could get it to do what i am trying to do here. I want to take a snapshot of B2:B30 (the odds) if I62=1 and place the figures in F64:F92. I62=1 15mins before the time that is the first 5 characters of the sheets name, expressed in the time form: 00h00. The sheets name is different every time it is opened, but the time format at the beginning is the same. I am sure there is much more elegant ways to do most of the things i am doing on here, but i have been using this to self teach and it has turned into a bit of a project now so do not be too harsh. Remember, all the decent coding was already there, i have probably massacred a very good spreadsheet, but it has been 'fun'. if i am trying to do the impossible, no bother, but i guess if the sheet is capable of doing what it does already, then this should be ok?

Regards, Jamie

[jamiepullen]

by the way, i know a lot of it is messy and has irrelevant formulae in places, but i plan to tidy it up before i give him a copy back.

[jamiepullen]

i can also post the original untouched (unmassacred) version on here if needed.

[jamiepullen]

i have used richards suggestion and written a macro that when played, copies the cells and pastes them where needed. is there now any way that i can get this macro to run if I62=1 on any open sheet in the workbook?

[jamiepullen]

i have:

Please Login or Register  to view this content.

That when run, does what i want. from googling, i think that something like:

Please Login or Register  to view this content.

Will start the macrojamie if I62=1. Bear in mind i cobbled this code together from stuff i found on the internet. Where do i put this code so that I62 on any sheet will start macrojamie if it =1?

[Tom Schreiner]

I appears that the querytable is "technically" the leading precendent of range I62. Is this correct? In other terms, cell F52, which contains "=Now()", an intermediary precedent of I62, is updated when your query is refreshed every 15 minutes. If this is the case, the most concise way to do this would be to use the querytable's AfterRefresh event to check for a value of 1 in I62 and then respond accordingly. What do you guys think?

[jamiepullen]

what would i need to look up to do this? i know the sheet is a mess at the moment and i will be removing most of it. I have been googling and the other way i thought of was to maybe somehow use the ontime function?

i really have no vba experience past recording a macro, any help would be appreciated

Regards, jamie

[Tom Schreiner]

If you look at the code in your class modules, there is some code there utilizing the events of your querytables...