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

 

[danjuma]

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

Awesome! Thank you very very much. I am most grateful!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[danjuma]

Hello. With regards to my original request which ended up with the code below as the solution (many thanks Fluff for your assistance), how can I amend the code to save the file as a pdf instead of an excel file please? I am guessing the file has to be send as print to pdf and then saved as pdf, but don't know how to code this. Many thanks.

Sub SaveExcelFile()
ActiveSheet.Unprotect Password:="password"
 ActiveSheet.Copy  ' creates new workbook
 ActiveSheet.Range("A6:L29").Interior.ColorIndex = 2
 d = ActiveSheet.Range("B3")
 ActiveSheet.Protect Password:="password"
 strFileName = "C:\Users\Kaye\Desktop\”
 Application.DisplayAlerts = False
 ActiveWorkbook.SaveAs strFileName & Format(d, "dd-mm-yyyy") & " Variation Sheet.xlsx", 51
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub

 

[Fluff]

As this is a different question, it needs a new thread. Thanks

 

[danjuma]

As this is a different question, it needs a new thread. Thanks

Thanks for the reply Fluff. I think I have figured it out.