LinkBack URL
About LinkBacks
When the data is refreshed, how can i keep the previous data and use it in a graph that automatically updates?
Thanks
When the data is refreshed, how can i keep the previous data and use it in a graph that automatically updates?
Thanks
Last edited by no.18shirt; 07-06-2009 at 02:44 PM.
How about creating a worksheet_change macro that copies all the query data to another duplicate area nearby, we'll call that "ONDECK" area. Then there's a third area with the previous "ondeck" data now serving officially as "PRIOR DATA".
So, 3 sections:
1) Main query area where data is being refreshed.
2) "On deck" - duplicate of the current main query data
3) Prior data.
When the main query updates, this should trigger your worskheet_change macro. What it should do, in this order, is:
a) copy section 2 over section 3 so the old "ondeck" data is now in the "prior data" area
b) copy the newly arrived section 1 data into the "ondeck" area.
c) cause your charts to refresh
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
o.k thanks. How do I find the on deck area or is this something I have to make myself? If so how? Thanks a lot for your help.
Of course, I'm suggesting you create these areas yourself. I often create backup data sections by just adding an "A" to the front of the original range. For instance, if the webquery data arrives in cells A1:A10, I would have the "On Deck" area as AA1:AA10, and the "Prior Data" as BA1:BA10.
Here's a worksheet_change macro that does what I've suggested.
Please Login or Register to view this content.
o.k I have no idea how to make macro's (if thats what your doing). Am I just ment to copy and paste the original data or not?
There is also insert entire rows for new data option. Will this have the same effect? If so how do I form that data into a graph?
Thanks
.................
1) Identify the range of cells your web query is updating. In my macro it is looking at cells A1:A10. You need to replace that with your actual range to watch. Then you need to pick matching "ranges" in two other places to serve as "on deck" area and the "prior data" areas. The macro is using AA1:AA10 as ondeck, and AB1:AB10 as prior data ranges. Change those to match your actual range.
After you've edited the macro for your working environment (the colored ranges I've already explained), then here's how to install the macro into your sheet:Please Login or Register to view this content.
A. Right-click on the sheet tab name and select View Code
B. The VB Editor will open, paste in the edited code
C. Get out of VBA (Press Alt+Q)
D. Save your sheet
The macro is installed and live. The next time the web query updates the red section, the copying will occur.
2) Once that is working properly, highlight the "ondeck" area and the "Prior Data" area and activate the Chart Wizard, use it to help you create your bar graph.
o.k so where abouts do I put the code that you have above? And what do I use to make it?
Thanks a lot
The step-by-step instructions, line by line, I provided in post #7 is the best I can do.
If there is something in my instructions above you don't understand, ask for specific clarifications. Otherwise, just do exactly what I instructed, line by line, in post #7.
Is it possible to have the data coppied into the next column along each time the data is refreshed done automatically? This way there will be a record of all previous data which could be automatically uploaded into a graph. Would this be possible?
Try something like:
Please Login or Register to view this content.
great, thanks! How do I get the graph to continually update automatically? Over time there will be a lot of data so how can all this be put into a graph? Thanks
Graphing isn't my thing.
Post a new specific question in the Charting Forum and include a link back to this thread as reference.
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].
(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)
I hope this doesn't bother anyone but this script doesn't work for me. I'm using excel 2003. I have a query updating from a we page, next to it I modify the data so I can work with it. If I use the script and just modify A1:A10 (also AA1:AA10 etc) into M4:Q51 it doesn't work. I've updated the table and nothing.
Can you please help me?
What I need to do is get some data with a web query, I them in the next cells use only what I need and I modify the data (I can't select in the web query only half of table and some columns in the middle are useless to me).
Then I need to save it, so that when I update the query I will still have the previous data. This is for a weather gathering tool.
Thank you very much.
JBeaucaire I am trying this code today with Excel 2010 and it doesn't seem to be working for me. I source for the query is another excel file, would it not work unless the query is coming from the web?
"Source the query to another workbook" sounds like codespeak for "I use a formula".
The macro in this thread supposing that cells are being physically updated, so if they are, I would expect the "worksheet_change" to trigger. If it's formulaic, no, that's not really a change to the cells, it's simply a change to the displayed values of the formulas.
If you can't work it out, start a new thread of your own, GO ADVANCED and paperclip your sample files and I'm sure you'll get rapid assistance. You can private message me a link to your thread, if you wish.
Hello. I am a new member to this forum which I have been using as a resource for a few weeks now.
That said, I am hoping someone can help me with a specific question that is similar to the one posed in this thread.
I am trying to use the code listed in post # 11. My problem is twofold. I am just starting out with advanced excel functions and I can not seem to modify the query below to work for my particular situation.
I am trying to achieve the following:
I have data in a workbook with a pivot table tab that is connected to a data file via a query. This pivot table refreshes everytime i open the file. The only thing that changes is the data, the cell locations are fixed. I have created a second tab on this workbook which links to the pivot table and takes the figure totals and puts them in this second tab in a nice uncluttered display. What I would like to do is to have this second tab archive all of the data that is currently purged every time I open and refresh the workbook. to say it another way, I would like the data in B2:B150 which is pulling from 'pivottable'!AA28 - "pivottable"!AA176 to be moved over to C2:C150 when i open and refresh the workbook. Then everytime I open the workbook after that they data would continue to move right with the most current data being in column B, the second to most current data in column C, and so on.
Any help you can give is GREATLY appreciated.
Welcome to the Forum. When you registered just now you promised to follow the forum rules, but in haste perhaps you forgot to actually read them. Please take a moment and do so now. Note particularly rule #2
=========
Unfortunately your post above does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks