asked on Need Store and Retrieve Macro for recalling original value set
EE Pros,
I am looking for help with macros for a workbook I'm working on that simply takes a particular range name that is selected and posts it to another Data Store Tab. Changes can then be made to the original data set and either be overwritten by the original data set (now on the Data Store Tab) or can be stored and overwrite the original data on the Data Store Tab.
Attached is a mockup Workbook for your review.
Thank you in advance for the help!
Bright01
Store-and-Retrieve-Macro.xlsx
I am looking for help with macros for a workbook I'm working on that simply takes a particular range name that is selected and posts it to another Data Store Tab. Changes can then be made to the original data set and either be overwritten by the original data set (now on the Data Store Tab) or can be stored and overwrite the original data on the Data Store Tab.
Attached is a mockup Workbook for your review.
Thank you in advance for the help!
Bright01
Store-and-Retrieve-Macro.xlsx
Microsoft ExcelMicrosoft Office
Last Comment
Tracy
Why not just copy the entire worksheet used range?
ASKER
Great question. Presently, what I have is a formula based model that produces certain results. I would like to have a save/restore system that only deals with a single column (identified as a named range) being able to move it back and forth in a data store without having to leave the WS for another WS. I plan to store 50 to 100 different columns so it would be complicated from a user perspective to restore a WS vs. simply selecting a particular stored rangename or column selection.
Make sense?
B.
Make sense?
B.
Can I ask what the underlying purpose of this is? It feels sort of like you want to be able to do some "what if" sort of work, and if the changes made don't work out you want to be able to undo them?
If that is the case I would think about making your "save" chronological in nature, rather that a block of the sheets data. It feels like it would be easy when you have many of these saved "chunks" of the sheet to mix and match them wrong and get the data out of balance. Whereas if you were able to take a "snapshot" of the whole sheet and label it with a comment and time stamp, then if you need to return to it you have a cohesive data-set that worked up to that point.
The saves could be the whole workbook to files with special names, or just new hidden sheets arranged by date.
I may be completely missing your use case, but just wanted to pass this thought along.
»bp
If that is the case I would think about making your "save" chronological in nature, rather that a block of the sheets data. It feels like it would be easy when you have many of these saved "chunks" of the sheet to mix and match them wrong and get the data out of balance. Whereas if you were able to take a "snapshot" of the whole sheet and label it with a comment and time stamp, then if you need to return to it you have a cohesive data-set that worked up to that point.
The saves could be the whole workbook to files with special names, or just new hidden sheets arranged by date.
I may be completely missing your use case, but just wanted to pass this thought along.
»bp
You don't have to leave a worksheet to transfer data.
ASKER
Bill,
To answer your question; This is a program for inputing 12 values (basecase), doing some modeling on those values (what ifs) which create a current case. By storing the defined rangenames, I hope to be able to retrieve a basecase and re-model the data, again, storing it for reuse. I had hoped in my question/example, I would get a chance to see how the code may be structured to store/retrieve data without leaving the primary WS. There are a number of additional aspects to this simple routine such as an alert before overlaying existing "same name" data. As with many EE questions, they build on themselves as a novice such as myself learns from what the Experts demonstrate. I typically author 3 to 5 questions on the same routine / result I'm trying to accomplish and it builds both the capability and my personal knowledge of VBA.
Hope that helps with regard to the intent of the "ask".
To answer your question; This is a program for inputing 12 values (basecase), doing some modeling on those values (what ifs) which create a current case. By storing the defined rangenames, I hope to be able to retrieve a basecase and re-model the data, again, storing it for reuse. I had hoped in my question/example, I would get a chance to see how the code may be structured to store/retrieve data without leaving the primary WS. There are a number of additional aspects to this simple routine such as an alert before overlaying existing "same name" data. As with many EE questions, they build on themselves as a novice such as myself learns from what the Experts demonstrate. I typically author 3 to 5 questions on the same routine / result I'm trying to accomplish and it builds both the capability and my personal knowledge of VBA.
Hope that helps with regard to the intent of the "ask".
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Brad,
Thank you very much for the effort on this. My mother-in-law has been in Rehab for the past week and it has consumed most of my time. I will look at this today. Again, and as always, thank you for the response and I'll be back on this shortly.
Thank you very much for the effort on this. My mother-in-law has been in Rehab for the past week and it has consumed most of my time. I will look at this today. Again, and as always, thank you for the response and I'll be back on this shortly.
Jim,
After my post, I thought about whether you might prefer displaying a userform to select the variables to be stored/retrieved. The userform could use checkboxes in a three column list (checkbox, range name, description), with the latter two taken from a Data worksheet. Or it could have a multi-select listbox control more like your original request with that same information.
I suggest against hard-coding the range names (and descriptions) in VBA code. Doing so would make the code harder for you to maintain.
Brad
After my post, I thought about whether you might prefer displaying a userform to select the variables to be stored/retrieved. The userform could use checkboxes in a three column list (checkbox, range name, description), with the latter two taken from a Data worksheet. Or it could have a multi-select listbox control more like your original request with that same information.
I suggest against hard-coding the range names (and descriptions) in VBA code. Doing so would make the code harder for you to maintain.
Brad
ASKER
Brad, This is a good first step for what I'm trying to do with Store and Retrieve. I'll build on it and submit a related question in a few days. I'm still at the Rehab. Center with my Mother-In-Law but should have some time to work on it later this week.
Will also consider a User Form but since there are more then 3 columns, will probably prefer a rangename drop down list since it will be ever expanding as things are saved. The checkbox would work if there were a limited number of cases to retrieve but I need something similar to what you provided several months ago with the Workbench and selecting a "Demo".
As always, "thank you"!!!
B.
Will also consider a User Form but since there are more then 3 columns, will probably prefer a rangename drop down list since it will be ever expanding as things are saved. The checkbox would work if there were a limited number of cases to retrieve but I need something similar to what you provided several months ago with the Workbench and selecting a "Demo".
As always, "thank you"!!!
B.
Jim,
A data validation dropdown allows just one selection of a range name.
A forms control listbox or combobox allows multiple selections--but is ugly when you plop it on top of a range of cells. That's why I was suggesting a userform.
Brad
A data validation dropdown allows just one selection of a range name.
A forms control listbox or combobox allows multiple selections--but is ugly when you plop it on top of a range of cells. That's why I was suggesting a userform.
Brad
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I have recommended this question be closed as follows:
Accept: byundt (https:#a42359686)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer
I have recommended this question be closed as follows:
Accept: byundt (https:#a42359686)
If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.
broomee9
Experts-Exchange Cleanup Volunteer