Error when saving a new file using SaveAs function in vba

Question

I am accepting a date from Input Box and filtering my data and saving it in a new workbook. When I am saving this new workbook, its giving me a Run-time error 1004 with a sentence as:

Method 'SaveAs'of object'_Workbook' failed.

I am unable to find a solution to this.

Sub GIACTSDS121()
   Dim dte As Date
   mBox = InputBox("Enter a date")

   If IsDate(mBox) Then
      dte = CDate(mBox)
      Dim Lastrow As Long
      Lastrow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
      ActiveSheet.Range("A1:AC" & Lastrow).AutoFilter Field:=2, Criteria1:=">=" & dte, _
              Operator:=xlAnd, Criteria2:="<" & dte + 1

      Range("U1").Select
      ActiveSheet.Range("A1:AC" & Lastrow).AutoFilter Field:=21, Criteria1:="Yes"


      Range("A1").Select
      Range(Selection, Selection.End(xlDown)).Select
      Range(Selection, Selection.End(xlToRight)).Select
      Selection.Copy
      Workbooks.Add
      ActiveSheet.Paste
      ActiveSheet.Range("A:A,E:E,I:I,M:N,Q:T,X:Z").EntireColumn.Delete

      ActiveWorkbook.SaveAs Filename:="K:\K_Drive\RP\RPS-Metrics-ops\' Operations Metrics\Investigation Documentation\GIACT Investigations\SDS_Cases\_" & dte & ".xlsx", FileFormat:=51
      ActiveWorkbook.Close

   Else
     MsgBox "This is not a date. Please try again"

   End If
End Sub

score 1 · Answer 1 · answered May 19 '20 at 11:33

1

The Filename parameter passed to SaveAs contains an invalid character that windows does not accept for a filename

Filename:="K:\K_Drive\RP\RPS-Metrics-ops\' Operations
                                         ^
                                         |
                                 maybe this is the cause!

answered May 19 '20 at 11:33

Ahmad

12,336
6
48
88

The file is getting saved for me in the desired folder but if my friend tries on their system, its giving the same error. No changes and the link given for saving iis a shared drive link which we both access – Nabeel razaq May 19 '20 at 11:58
In this case, make sure your friend has access to the saveas folder and that he/she has the right to create a new file inside that folder. – Ahmad May 19 '20 at 12:02

score 0 · Answer 2 · answered May 19 '20 at 13:30

Get rid of all ActiveSheet, ActiveWorkbook and all .Select if possible (see How to avoid using Select in Excel VBA).

Also specify a worksheet for every object that is located in a worksheet like Range, Cells, Rows, Columns.

Public Sub GIACTSDS121()
    Dim ws As Worksheet
    Set ws = ActiveSheet 'better define by name as: Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim mBox As Variant
    mBox = InputBox("Enter a date")

    If IsDate(mBox) Then
        Dim dte As Date
        dte = CDate(mBox)

        Dim LastRow As Long
        LastRow = ActiveSheet.Range("A" & ws.Rows.Count).End(xlUp).Row
        ws.Range("A1:AC" & LastRow).AutoFilter Field:=2, Criteria1:=">=" & dte, _
                Operator:=xlAnd, Criteria2:="<" & dte + 1

        ws.Range("A1:AC" & LastRow).AutoFilter Field:=21, Criteria1:="Yes"

        ws.Range(ws.Range("A1"), ws.Range("A1").End(xlDown).End(xlToRight)).Copy

        Dim NewWb As Workbook
        Set NewWb = Workbooks.Add

        NewWb.Worksheets(1).Paste
        NewWb.Worksheets(1).Range("A:A,E:E,I:I,M:N,Q:T,X:Z").EntireColumn.Delete

        NewWb.SaveAs Filename:="K:\K_Drive\RP\RPS-Metrics-ops\' Operations Metrics\Investigation Documentation\GIACT Investigations\SDS_Cases\_" & dte & ".xlsx", FileFormat:=51
        NewWb.Close SaveChanges:=False
    Else
        MsgBox "This is not a date. Please try again"
    End If
End Sub

Error when saving a new file using SaveAs function in vba

2 Answers2