Debug wrong number of arguments or invalid property assignment error.

[Harshil Mehta]

I have 7 sheets in total and want to make save as for the first 5 sheets with time stamp and password.

The below code gives an error msg "wrong number of arguments or invalid property assignment" highlighting the word FORMAT.

This code used to work perfectly before adding the the 7th sheet. The 7th sheet is a place where Raw Data is dumped and with the help of the VBA Code the data is populated into different sheets.

Could anyone please help me?

Sub Make_SaveAs()
Dim ArrayOne() As String
Dim Mypassword As String

Application.ScreenUpdating = False

Mypassword = Sheets(6).Range("E2")
ReDim ArrayOne(1 To 5)

 For i = 1 To 5
      ArrayOne(i) = Sheets(i).Name
 Next

Worksheets(ArrayOne()).Copy
With ActiveWorkbook
    .SaveAs Filename:=Environ("USERPROFILE") & "\Desktop\" & Format(Now(), "YYYYMMDD -") & " Client Design - " & ThisWorkbook.Sheets(6).Range("D2") & "_" & ThisWorkbook.Sheets(6).Range("C2") & ".xlsb", FileFormat:=xlExcel12, Password:=Mypassword
    .Close SaveChanges:=False
End With

Application.ScreenUpdating = True

MsgBox "File Saved on Deskstop"

End Sub

 

[Harshil Mehta]

Make sure that you do not have subs, functions or variables called Format

I dont have any variables called Format. This the complete code. If I remove the entire format part then the code saves the file without time stamp.

 

[RoryA]

Change the format line to use VBA.Format instead. If that fixes it, then somewhere in the code in your workbook (not necessarily in that routine or even module), you do have something called Format.

 

[Harshil Mehta]

Change the format line to use VBA.Format instead. If that fixes it, then somewhere in the code in your workbook (not necessarily in that routine or even module), you do have something called Format.

It worked. Thanks

 

[davesexcel]

Asked here. Different title.

Make save as for all sheets except last two

I have 7 sheets in total and want to make save as for the first 5 sheets with time stamp and password. The below code gives an error msg "wrong number of arguments or invalid property assignment&

stackoverflow.com

 

[Harshil Mehta]

Asked here. Different title.

Make save as for all sheets except last two

I have 7 sheets in total and want to make save as for the first 5 sheets with time stamp and password. The below code gives an error msg "wrong number of arguments or invalid property assignment&

stackoverflow.com

Yes, as I m a beginner and needed to fix the issue on urgent basis and so reached large group of people for help.