Run-time '70': Permission denied

[danjuma]

Hello. I have created to code below which is basically meant to create a separate workbook from the contents of the current worksheet the code is on, and save it to the specified directory using the date in cell B3 as part of the file name and as a .xlsx (don't want it to be a macro-enabled workbook for some other reasons). However, I keep getting "Run-time '70': Permission denied" and not sure what I am doing wrong. This is my home PC and I have all permissions on it. Thanks

Private Sub SaveVS_Click()
  ActiveSheet.Unprotect Password:="mypassword"
  ActiveSheet.Copy  ' creates new workbook
 ActiveSheet.Range("A6:L29").Interior.ColorIndex = 19
 Date = ActiveSheet.Range("B3")
 ActiveSheet.Protect Password:="mypassword1"
  strFileName = "C:\Users\Kaye\Desktop\"
  ActiveWorkbook.SaveAs Filename:=Path & Date & "\Variation Sheets.xlsx"
End Sub

 

[Fluff]

You cannot use Date as a variable, change it to something else.

 

[danjuma]

You cannot use Date as a variable, change it to something else.

Hi, sorry, I actually realised this and changed it during my trying to work out what was wrong. Anyway, I have changed Date to just D, but now getting the following errors

 

[Fluff]

What is Path meant to be?
Also what is in B3

 

[danjuma]

What is Path meant to be?
Also what is in B3

I thought Path would be referring to "C:\Users\Kaye\Desktop\"? B3 has date in the format dd-mm-yyyy (eg. 02-09-2021)

 

[Fluff]

Do you have a folder on your desktop called 02-09-2021?

 

[danjuma]

Do you have a folder on your desktop called 02-09-2021?

No, no folder. Idea is for workbook to be saved to desktop with a filename like 02-09-2021 Variable Sheets.xlxs

 

[Fluff]

Ok, try it like

Private Sub SaveVS_Click()
  ActiveSheet.Unprotect Password:="mypassword"
  ActiveSheet.Copy  ' creates new workbook
 ActiveSheet.Range("A6:L29").Interior.ColorIndex = 19
 d = ActiveSheet.Range("B3")
 ActiveSheet.Protect Password:="mypassword1"
  strFileName = "C:\Users\Kaye\Desktop\"
  ActiveWorkbook.SaveAs strFileName & Format(d, "dd-mm-yyyy") & " Variation Sheets.xlsx", 51
End Sub

 

[danjuma]

Ok, try it like

Private Sub SaveVS_Click()
  ActiveSheet.Unprotect Password:="mypassword"
  ActiveSheet.Copy  ' creates new workbook
 ActiveSheet.Range("A6:L29").Interior.ColorIndex = 19
 d = ActiveSheet.Range("B3")
 ActiveSheet.Protect Password:="mypassword1"
  strFileName = "C:\Users\Kaye\Desktop\"
  ActiveWorkbook.SaveAs strFileName & Format(d, "dd-mm-yyyy") & " Variation Sheets.xlsx", 51
End Sub

Brilliant! That works. Only issue is that instead of saving it straight away, I still get the below prompt (image below) and have to click 'Yes' to continue. If this could be avoided, that would be awesome, if not I will just have live with it

 

[Fluff]

Yup, just add these two lines before & after the saveas

  Application.DisplayAlerts = False
  ActiveWorkbook.SaveAs strFileName & Format(d, "dd-mm-yyyy") & " Variation Sheets.xlsx", 51
  Application.DisplayAlerts = True