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
 

Harshil Mehta

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

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

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

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

Harshil Mehta

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
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