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

 

[davesexcel]

Is it possible your desktop is connect to OneDrive?
Look at the differences between these two msgboxes.

MsgBox Environ("USERPROFILE") & "\Desktop\"
MsgBox CreateObject("WScript.Shell").specialfolders("Desktop") & "\"

 

[Harshil Mehta]

Is it possible your desktop is connect to OneDrive?
Look at the differences between these two msgboxes.

MsgBox Environ("USERPROFILE") & "\Desktop\"
MsgBox CreateObject("WScript.Shell").specialfolders("Desktop") & "\"

My system is not connected to OneDrive.

 

[davesexcel]

So was there a difference in the msgboxes?

Step through the code and see if the string variables do not contain illegal characters.

Sub Make_SaveAs()
    Dim ArrayOne() As String
    Dim Mypassword As String
    Dim dr As String, dt As String, nm As String, fBM As String
    Dim sh As Worksheet
    Set WS = Sheet6
    dr = CreateObject("WScript.Shell").specialfolders("Desktop") & "\"
    'dr = "C:\Users\davem\OneDrive\Desktop\"
    dt = "Client Design - " & Format(Now(), "YYYYMMDD -")
    nm = WS.Range("D2").Value & "_" & WS.Range("C2").Value & ".xlsb"
    FNM = dr & dt & nm
    Application.ScreenUpdating = False

    Mypassword = WS.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:=dr & dt & nm, FileFormat:=xlExcel12 ', Password:=Mypassword
        .Close SaveChanges:=False
    End With

    Application.ScreenUpdating = True

    MsgBox "File Saved on Deskstop"

End Sub

 

[Harshil Mehta]

So was there a difference in the msgboxes?

The result was the same, ERROR, highlighting the word FORMAT. The error is not regarding the msgboxes

 

[Harshil Mehta]

Attached is an image of the code. Highlighting the word FORMAT in blue color.

 

[davesexcel]

Okay. I was not getting a format error.

 

[Harshil Mehta]

Okay. I was not getting a format error.

Yes, you wont see a format error if you simply add the 7th sheet. I dont understand what could be the issue.

 

[RoryA]

It sounds like you created a routine/variable called Format.

 

[Fluff]

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