VBA to create backup of user input on protected sheets

I have a master spreadsheet with several sheets that allow the user to input their own custom data and they can then save the master including their custom inputs.

I have made some changes to the master that I want to now give to the users, but I don't want them to lose all of the custom things they input. The sheets are all protected, but the cells where the user can put their own data are not locked.

I want to have a macro that will copy the unprotected cells in specific, named sheets, and copy them to a second workbook, with the same sheet names and save the new file with the current date at the end of the filename. I will ultimately want a second macro to then upload the backed-up data back into the new file that I send to them. (I'll create a separate post for that if I need help.)

I have found code to do a few of these things, but don't know how to put them all together.

Code to open the new file (Target backup file for copying into): this works great for opening the initial empty backup file

Please Login or Register  to view this content.

Then I have this, but don't know how to combine them to work together. I want to take out the specific path to open the backup and insert the above code to allow the user to go find the file and then it sets the wbTarget as the file that they opened.

Please Login or Register  to view this content.

I found this to save a file with the current date at the end, but I don't want to specify the path. I want it to go to the same folder where they originally chose the backup file to open:

Please Login or Register  to view this content.

Any help to piece this together is appreciated. I posted so much because I didn't want anyone to think that I haven't tried to look this up myself. I am just not good with code and need help.

This worked!

Now, to expand on this, if I want to copy additional sheets, can I just copy this line and paste it underneath this line with the names and ranges of the other sheets? Or is there something more involved than that?

Please Login or Register  to view this content.

Originally Posted by Mumps1

What are the names of the other sheets? Is the range to be copied the same on all sheets?

I have 7 other sheets, named as follows:
Preferences-backup
Main Ingredients List-backup
Main Materials List-backup
Batter-Icing Amts-backup
Categories-backup
Recipes-backup
Tools-Inventory-backup

There are multiple ranges on each sheet, and none of them are the same. (I know....I'm difficult )

No, they are all sheets in the same workbook as the "Company Info" sheet. The sheet names are the same as the sheet names in the original Active Workbook, with "-backup" added to them. I did that so I could tell them apart in the code lines.

Basically, it is all of the unlocked cells on each sheet. Since it is quite a few ranges on each sheet, and they are all different, I will just give all of the different cells/ranges on the Company Info Sheet. I was hoping to be able to adapt the ranges for each sheet without bothering you for the specifics.

On the original code that you provided, I had to remove the .unprotect/.protect line items for it to work. I don't need it to unlock since the only cells I will be copying will be unlocked already. I only protected the sheets to keep users from accidentally deleting formulas.

Here are the ranges on the Company Info Sheet of the Active Workbook (They are merged cells in most cases):

D8:K9
D11:K11
D12:F12
H12
J12:K12
D12:K13
D13:K15
D16:F16
H16
J16:K16
D19:F19
D21:F21
D23:H23
D25:H25

So sorry. I actually changed the range being copied and where I pasted it. I put in "D8:K8" and pasted to D8.

Ideally, I would like to paste the items in the same cells they originally came from into the same spot in the wbTarget file. Does that make sense?

I am going in and adding in 1 range from each sheet just to test, but let me make sure that I understand the names of the sheets as you listed them.

Please Login or Register  to view this content.

Are these the sheet names for the workbook being copied, or the Target workbook (being copied to)?

The workbook sheets being copied FROM do not have "-backup" and the workbook being copied TO does.

I changed the names slightly to make sure they matched the spreadsheets and added a range for each sheet. Here's the code I just tried to run:

Please Login or Register  to view this content.

I received this error:
Copy-Paste1.jpg

Wait....I see the error. Let me try again!

I really appreciate all of your help so far!

I changed the names on the Target worksheet to be the same as the Original worksheet, just to simplify things (one of the names was too long to add "-backup")

I added 1 range for each sheet just to test that each one would work.

Please Login or Register  to view this content.

I am still getting the following error:
Copy-Paste2.jpg

Well, I posted a long reply, but apparently it did not save it. I got it to work! Thank you so much for your help!

Here's the final code:

Please Login or Register  to view this content.