VBA error when using "Save As" to save a copy

Hi Guys,

I have the following code in my workbook under "this_workbook"
I have a user form where different people have different logins and when they login they have access to different sheets. I would have access to all sheets that I would not like staff to access. I cannot remember the initial problem, but sometimes when I logged out staff could see my sheets when they logged in so someone helped me with the below code so every time anyone closed the workbook all sheets are hidden first then it shuts down, this fixed the error where the person coming after me could only see the splash sheet while they logged in then only gained access to their own sheets.

The issue I am having is, once a week just to be cautious I save a backup of this workbook on a separate drive on the computer by using "save as". (I have no access to cloud backup in work) This works fine and I "save as" worksheet 1 (backup) then I close it and all sheets hide. However when I go to open the original worksheet 1 all previous sheets are still open. I know this is because by using the "save as" the original one never got to close properly therefore the below code never kicked in.

Any suggestions how to work around this? Now that I know it happens I always go straight back to the original open it and close it too but that's a bit of a clunky workaround

Please Login or Register  to view this content.

Thanks in advance

Thanks JLGWhiz,

I am using excel 2003, when I click on file, there is no savecopyas, just save, save as, save as workspace

When would this code be activated and also note that some users of the spreadsheet would not have access to the drive I’d normally save it in. It’s a managers drive that I can access on any computer with my login. No one else can log into it. I expect this would cause an error

Originally Posted by JLGWhiz

The SaveCopyAs is a VBA method. You would need a simple macro to do the job.

Please Login or Register  to view this content.

Example of path and file name: "C:\Temp\myXLfiles\myFilebk.xlsx"
The code would go in the public code module1. You would need to be sure your workbook is a macro enabled workbook with the .xlsm file extension.