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]

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.

You appear to misunderstand what I'm trying to do and I may not be understanding your solution. Your solution doesn't appear to have a way to update the "Master", which in my case would be the codes sheet. My data page is the master.

In a nutshell, I'm trying to create a tool that will validate adjustments submitted to leadership. These adjustments must be submitted by leadership to the IT department to be done in batch. The problem we're having with so many people submitting is that when one person messes up, the entire batch fails.

I want to create something that, if approved, can be sent to everyone. The problem I have are the adjustment codes. Currently, they are listed on a particular document that I don't control and I'm scraping the data to my workbook. I want to have a macro that can refresh the codes page and save it to their template. I don't want to have to redistribute new templates every time the document is updated. I want them to be able to update it to their template and save it there, but I don't want them to be able to save the template with data on it. Saving a local renamed copy would be fine. I'm trying to remove their ability to save over the original template while still being able to update that particular sheet.

My other consideration is to be granted a folder on the network and have them open their templates from a shortcut. That way the adjustment codes can be updated in a central place by me or someone else without having to scrape the document. The codes document itself could eventually be formatted in such as way that the scrape fails.

Once they use the template and all the data is validated, my macro will create a sheet with only the text to submit.

 

[TryingToLearn]

My other consideration is to be granted a folder on the network and have them open their templates from a shortcut. That way the adjustment codes can be updated in a central place by me or someone else without having to scrape the document. The codes document itself could eventually be formatted in such as way that the scrape fails.

This is what I was suggesting where everyone uses a given template and then saves their own end result.

 

[AndyTampa]

This is what I was suggesting where everyone uses a given template and then saves their own end result.

This would be preferred, but will likely not be the result. That said, I'm not sure how to do this either. Do I distribute a macro that loads this macro? A shortcut that loads the macro? Or do I simply distribute the template that looks for the codes at a network location?

 

[TryingToLearn]

A shortcut that loads the centrally located template. This allows the one template to be changed as necessary instead of the unwanted having to distribute the changed template. If you need help with the shortcut for the network, that is definately out of my comfort zone.