Save workbook to new location without overwriting it

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
587
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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

RobP

Active Member
Joined
May 8, 2008
Messages
335
Office Version
  1. 365
Platform
  1. Windows
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
 

RobP

Active Member
Joined
May 8, 2008
Messages
335
Office Version
  1. 365
Platform
  1. Windows
apologies - I've just reread the last part of your ticket :) doh !!

Sorry
Rob
 

RobP

Active Member
Joined
May 8, 2008
Messages
335
Office Version
  1. 365
Platform
  1. Windows
would this post help you in your quest ?


Rob
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
20,524
Office Version
  1. 365
Platform
  1. Windows
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!
 
Solution

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
587
Office Version
  1. 365
Platform
  1. Windows
Thank you both for responding. The SaveCopyAs works perfectly! I have never used that option but I will from now on.
 

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
587
Office Version
  1. 365
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,176,169
Messages
5,901,737
Members
434,915
Latest member
MyLadysKnight

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
Top