Link to home
Start Free TrialLog in
Avatar of Bright01
Bright01Flag for United States of America

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!

Avatar of aikimark
Flag of United States of America image

Why not just copy the entire worksheet used range?
Avatar of Bright01


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?

Avatar of Bill Prew
Bill Prew

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.

You don't have to leave a worksheet to transfer data.

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".
Avatar of byundt
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

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.
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,  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"!!!

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.

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.

Experts-Exchange Cleanup Volunteer