Write Reserved? Only when saved by Macro!

jasgot

New Member
Joined
Jul 16, 2002
Messages
22
When I use the file menu to SAVE AS, it saves fine, this macro, however, fails with this message:
"Run time Error 1004
Operation Failed. Blah...BlahPrices.xls is write reserved."

Here is the macro:

If Application.UserName = "Jason Gottschalk" Then
Application.EnableEvents = False
ActiveSheet.Unprotect
Range("B73").Select
ActiveCell.FormulaR1C1 = ""
Range("G79").Select
ActiveCell.FormulaR1C1 = "0"
Range("B69").Select
ActiveCell.FormulaR1C1 = ""
Range("D69").Select
ActiveWorkbook.SaveAs Filename:="P:SYO Sales DocsPrices.xls"
ActiveWorkbook.Windows(1).Caption = ActiveWorkbook.FullName
Application.EnableEvents = True
End If

Any thoughts on why the macro fails, but I can do it manually?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Any password have to supply to unprotect the workbook?

gnaga
 

jasgot

New Member
Joined
Jul 16, 2002
Messages
22
I don't think so, but if I did, wouldn't I also have to do it when I save manually?
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Then why do you unprotect the workbook before changing the formula? Are you supplying any password to open the book manually?

GNaga
 

jasgot

New Member
Joined
Jul 16, 2002
Messages
22

ADVERTISEMENT

I unprotect the workbook because I have several locked cells and hidden formulas. One of the cells that this macro changes is a locked cell. There is no password for the protection.
 

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
700
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Ok. Then store the new path of the file including file name into a variable and then use this statement

NewPath = "P:SYO Sales DocsPrices.xls"

ActiveWorkbook.SaveAs(NewPath)

If there is no change in path and file name then use Save method instead SaveAs

GNaga
 

jasgot

New Member
Joined
Jul 16, 2002
Messages
22
That didn't work, however this did. But it scares me to use it. I never like deleting files.....

If Application.UserName = "Jason Gottschalk" Then
OriginalSheetPath = "P:SYO Sales DocsPrices.xls"
Application.EnableEvents = False
ActiveSheet.Unprotect
Range("B73").Select
ActiveCell.FormulaR1C1 = ""
Range("G79").Select
ActiveCell.FormulaR1C1 = "0"
Range("B69").Select
ActiveCell.FormulaR1C1 = ""
Range("D69").Select
FileSystem.Kill (OriginalSheetPath)
ActiveWorkbook.SaveAs (OriginalSheetPath)
ActiveWorkbook.Windows(1).Caption = ActiveWorkbook.FullName
Application.EnableEvents = True
End If
 

Forum statistics

Threads
1,144,219
Messages
5,723,076
Members
422,477
Latest member
pete101

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