Debug wrong number of arguments or invalid property assignment error.

Harshil Mehta

Board Regular
Joined
May 14, 2020
Messages
85
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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") & "\"
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Attached is an image of the code. Highlighting the word FORMAT in blue color.

vba error.PNG
 
Upvote 0
It sounds like you created a routine/variable called Format.
 
Upvote 0
Make sure that you do not have subs, functions or variables called Format
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top