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

[Nitro2481]

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

[JLGWhiz]

Try using 'SaveCopyAs" instead of "SaveAs". It should leave your original workbook intact and you can then close it via normal process.

[Nitro2481]

Thanks JLGWhiz,

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

[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.

[CK76]

VBA method should still be available I believe.

Please Login or Register  to view this content.

[Nitro2481]

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

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.

[JLGWhiz]

You would have to manually initiate the code. You can do that several ways.
1. Press Alt + F8, click on the macro name, then click 'Run'.
2. Use Keyboard shortcut key, assign the key by pressing Alt + F8, click Options and fill in the appropriate blocks in the dialog box.
3. Assign it to a button by going to 'Developer', 'Insert' and clicking on command button in the 'Form Controls' tool box. When you place the button on the sheet, complete the dialog box entries.

[JLGWhiz]

This post deleted..