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

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

gnaga

Well-known Member
Joined
Jul 9, 2002
Messages
742
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
742
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
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
742
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,181,450
Messages
5,929,981
Members
436,712
Latest member
meganisagee

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