Debug wrong number of arguments or invalid property assignment error.

Harshil Mehta

Board Regular
Joined
May 14, 2020
Messages
64
Office Version
  1. 2013
Platform
  1. Windows
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
Is it possible your desktop is connect to OneDrive?
Look at the differences between these two msgboxes.
VBA Code:
MsgBox Environ("USERPROFILE") & "\Desktop\"
MsgBox CreateObject("WScript.Shell").specialfolders("Desktop") & "\"
 

Harshil Mehta

Board Regular
Joined
May 14, 2020
Messages
64
Office Version
  1. 2013
Platform
  1. Windows
Is it possible your desktop is connect to OneDrive?
Look at the differences between these two msgboxes.
VBA Code:
MsgBox Environ("USERPROFILE") & "\Desktop\"
MsgBox CreateObject("WScript.Shell").specialfolders("Desktop") & "\"
My system is not connected to OneDrive.
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
So was there a difference in the msgboxes?

Step through the code and see if the string variables do not contain illegal characters.
VBA Code:
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

Board Regular
Joined
May 14, 2020
Messages
64
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
May 14, 2020
Messages
64
Office Version
  1. 2013
Platform
  1. Windows
Attached is an image of the code. Highlighting the word FORMAT in blue color.

vba error.PNG
 

davesexcel

Well-known Member
Joined
Feb 26, 2006
Messages
1,089

ADVERTISEMENT

Okay. I was not getting a format error.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,453
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It sounds like you created a routine/variable called Format.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Make sure that you do not have subs, functions or variables called Format
 

Watch MrExcel Video

Forum statistics

Threads
1,127,316
Messages
5,623,968
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top