I'm looking for suggestions how to save changes of one sheet only.

[AndyTampa]

I'm working on a macro that I can use at work and hopefully let others use. Right now I'm setting it up so that it checks input data in one column of the main sheet to data on another sheet with proper codes listed. But if it finds an error, I want the user to be able to refresh the codes sheet and rerun the macro.

What I'm looking to do is be able to save the refreshed data to the workbook without saving all the data the user input. I wanted to make this tool Read-only so they can't overwrite the tool itself.

I don't think it's as easy as saying "save worksheet to this file", but I don't know. A workaround I thought of would be to save the input data to a temporary workbook, erase it from the original workbook, save the whole thing, and then copy it back. Is there a better way? I'm a novice, so I may not understand some of the more complicated stuff. Please be gentle.

 

[AndyTampa]

EDIT) I'm not sure if this would work either:

How about if my macro doesn't actually let them input? What if it creates a whole new workbook and if they need to refresh, it refreshes the original, then copies it to theirs. I'd need to create the new workbook with the macros in it. I wonder if that would be too hard. It would allow them to save the data partway through if necessary.

Also, once the data is all validated, I'm going to add a macro to make a sheet for them to submit that doesn't contain the macros.

 

[AndyTampa]

Nothing? Nobody?

 

[Joe4]

I do not know of any way to save "part" of the file updates and not others. Save seems to be a pretty much "all-or-nothing" approach.
However, you can add a BeforeSave script that automatically clears out the section that you do not want them to save.

 

[AndyTampa]

That's my issue. They won't know if they need to update the sheet I need saved until the sheet on which they're entering data is full.
Maybe I could copy that sheet to a new workbook and clear it in the original. Save the original and copy it back. I can't guarantee that they won't already have an instance of Book2 or Book3 or whatever. Can I specify a name of a temporary workbook that doesn't get saved anywhere because I'm going to delete it anyway?

 

[Joe4]

It is hard to fully comprehend the whole structure of your workbook and your needs from a vague description without much detail (remember, while this problem may be very familiar to you, all that we know about it is what you posted here - we do not know at all what it looks like, how it works, and your needs).

From what I can glean, it sounds to me like you have a data input section of the workbook that you do not want to allow them to save to?
If that is the case, I fail to understand why a BeforeSave script that clears that out wouldn't work.
I have a feeling that is a lot more to this that you are not telling us.

It would be really helpful to me to see what we are working with (I tend to be a very visual person, so seeing what everything looks like and what is actually going on is a big help to me).

 

[AndyTampa]

I can't show you everything. There's just so much. This is a template I'm creating for people to use to create an actual sheet in the correct format so it doesn't fail processing.

On one tab is where they put their data. This data is tested line-by-line to see if the input is valid. One of the tests needs to check a column of codes and a column of adjustments against a list of valid codes on a second sheet. If the codes fail validation, I'd like them to be able to update this second sheet and save it to their template without saving their data too. That way they can validate their data against current info. I don't want to update the sheet every time they run the validation macro. I also planned on making the template read-only so they couldn't save over it without the password [which they won't have].

When they run the macro to update that one sheet, I'd like that macro to save only that sheet to their template without losing their data in the process.

My second option would be to put this macro in a public location so I can update it myself if the webpage from which I scraped the data changes. They could run a macro from there that creates a template for them to use locally with the most current information, but I don't have a public location. I'd need to ask for special permission from work and may not get it due to my role.

You said:

If that is the case, I fail to understand why a BeforeSave script that clears that out wouldn't work.

What do you mean by a "BeforeSave" script?
Are you saying to save the blanked template every time they close it?
Would that work if they simply X out of it?
Could it be coded to only save IF the page was updated?

 

[Joe4]

What do you mean by a "BeforeSave" script?

Automated VBA code that runs when someone tries to save the file.
See: Workbook.BeforeSave event (Excel)
and Workbook AfterSave and BeforeSave Events in Excel

Would that work if they simply X out of it?

If they simply "X" out of it without saving, then they wouldn't be saving the changes anyway, so it becomes a moot point.

The only concern is if they do not enable VBA code to run against the workbook (don't know if you already have other VBA code running against this workbook, so that may be a moot point too).
Another alternative is instead fo clearing it at the end before saving, clearing it when the file is first opened using a "Workbook_Open" event (see: Workbook.Open event (Excel)).
People sometimes "protect" against users disabling VBA by hiding most data sheets, and then unhiding the ones they need in the "Worbkook_Open" event.
So if they do not enable macros, they will not see those sheets, and not be able to use the workbook at all.

 

[AndyTampa]

Automated VBA code that runs when someone tries to save the file.
See: Workbook.BeforeSave event (Excel)
and Workbook AfterSave and BeforeSave Events in Excel


If they simply "X" out of it without saving, then they wouldn't be saving the changes anyway, so it becomes a moot point.

The only concern is if they do not enable VBA code to run against the workbook (don't know if you already have other VBA code running against this workbook, so that may be a moot point too).
Another alternative is instead fo clearing it at the end before saving, clearing it when the file is first opened using a "Workbook_Open" event (see: Workbook.Open event (Excel)).
People sometimes "protect" against users disabling VBA by hiding most data sheets, and then unhiding the ones they need in the "Worbkook_Open" event.
So if they do not enable macros, they will not see those sheets, and not be able to use the workbook at all.

I'm thinking about the options and I always come up with the same thing. In all instances, it seems the sheet I'm creating would re-download the codes every single time they use it which is what I'm trying to avoid.

FYI, The worksheet will need to have VBA enabled to run the validation check. The worksheet will not need to be saved, whether blank or full, unless the code sheet is changed. If it's changed, it will need to be saved, but it won't be saved if they X out of it. I don't need to clear the worksheet if I prevent them from saving it by making it read-only. If I add BeforeSave or AfterSave, it will do things I don't want even if they save it with another name, and that might change their data that they are actually trying to save.

I think I have to go the route of moving their data to another workbook and then back again after I save the codes.

 

[TryingToLearn]

IF I'm understanding your intention, this is way overcomplicated. Simply put,
1. you have a "master" workbook with the codes and routines in them.
2. You don't want users to be able to change the code and save it.
3. You DO want users to be able to fill in data, even if they don't save it.
Solution:

Create a workbook that will be the Template with the following:
a. two sheets
b. your codes are on 2nd sheet("Master") in which all cells are locked and the sheet is protected (and, if you like, data in locked cells is not visiable)
c. data entry is on 1st sheet("Data")
d. Insert a COMMAND BUTTON on data sheet that activates your macro to verify data.
e. Save it as an Excel Macro Enabled workbook (ie. verify.xltm)
f. when creating a new workbook by selecting the 'verify.xltm' Template, it will create a new workbook based on the template that will then execute your macros and can optionally be saved or discarded, neither of which will affect the original Template.

I have multiple templates like this and have macros to open the templates assigned to the customized ribbon for ease of access or you can pin the template to the File New menu.