Save workbook to new location without overwriting it

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
608
Office Version
  1. 365
Platform
  1. Windows
Hello,
Using the code below, completed workbooks are saved to a network drive each day. After saving it to the new drive, the user can delete the old data from the workbook on their computer and reuse it.
I noticed that after running the macro (saving to the network with the new name), you can accidentally delete the data and hit Ctrl+S and it clears it from the saved copy on the network drive.

Is there any way to prevent that from happening without closing the active workbook?

Any suggestions are appreciated. Thanks

VBA Code:
Dim wkb As Workbook
Set wkb = ActiveWorkbook
wkb.SaveAs "Z:\Projects\"new workbook name….."
 
Last edited by a moderator:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Rex,

reading above - you are suggesting that the workbook is "saved" after the work is "Completed" .... that means there should be nothing else done to it presumably.

Can you not add a line of code that actively closes the said workbook once it has been saved to prevent further modifications accidentally ?

Rgds
Rob
 
Upvote 0
apologies - I've just reread the last part of your ticket :) doh !!

Sorry
Rob
 
Upvote 0
would this post help you in your quest ?


Rob
 
Upvote 0
Try using the SaveCopyAs method instead...

VBA Code:
wkb.SaveCopyAs "Z:\Projects\NewWorkbookName.xlsx"

Note that if a workbook with the same name already exists on your network, it will automatically be overwritten.

Hope this helps!
 
Upvote 0
Solution
Thank you both for responding. The SaveCopyAs works perfectly! I have never used that option but I will from now on.
 
Upvote 0
Rob P - The post you added gave me another idea. There are times when someone forgets to save their workbooks to the Network. Based on that post, I think I can have it run automatically when the user closes the workbook. More ideas!
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top