VBA: saving workbook with password error

Where did you set the value of CBFile? It is not even dimmed.

Sorry!! forgot that bit... see below (my knowledge of VBA isn't great I'm afraid!)

Dim filepath2 As String
Dim Filename3 As String
Dim CBfile As String
Dim CB As String
Dim file_date As Date

file_date = Date
CB = "RRRP"
CBfile = "MyWORK " & Format(file_date, "mmm-yyyy ") & CB
filepath2 = "H:\Monthly MI\Excel_Suites\"
Filename3 = CBfile & " v1" & ".xlsm"
cbpass = "RRRP"

ActiveWorkbook.SaveCopyAs Filename:=filepath2 & Filename3, Password:=cbpass

If you want to change the password, then save your master file with the password, do your thing, and then change the master file's password to something else. As Compile will show you, SaveCopyAs only has one input parameter.

Tip: Debug.Print can be a good debugging tool to show results in the Immediate window after a run. e.g.

Sub ken()
  Dim filepath2 As String, Filename3 As String, CBfile As String, CB As String
  Dim file_date As Date, cbpass As String
  
  file_date = Date
  CB = "RRRP"
  CBfile = "MyWORK " & Format(file_date, "mmm-yyyy ") & CB
  filepath2 = "H:\Monthly MI\Excel_Suites\"
  'filepath2 = "c:\t\"
  Filename3 = CBfile & " v1" & ".xlsm"
  cbpass = "RRRP"
  
  Debug.Print filepath2 & Filename3
  
  ActiveWorkbook.SaveCopyAs filepath2 & Filename3
End Sub

That will add the password which can be different than the master file's password. Saving a Copy As is fast and leaves your master file still open. The solution depends on what your goals are.

Hi Ken

got it!

SO like you said, i changed it to SaveAs added in and it worked great! :D

Thank your for your help on this, was really appretiated!

for other peoples ref my final code was:

Dim filepath2 As String
Dim Filename3 As String
Dim CBfile As String
Dim CB As String
Dim file_date As Date
Dim cbpass As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

file_date = Date
CB = "RRRP"
CBfile = "MyWORK " & Format(file_date, "mmm-yyyy ") & CB
filepath2 = "c:\"
Filename3 = CBfile & " v1" & ".xlsm"
cbpass = "RRRP123"

ActiveWorkbook.SaveAs Filename:=filepath2 & Filename3, Password:=cbpass