CODEWITHSUNDEEP
excelvba
Nabeel razaq
Nabeel razaq

Reputation: 3

Error when saving a new file using SaveAs function in vba

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

Upvotes: 0

Views: 2192

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57673

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

Upvotes: 0

Ahmad
Ahmad

Reputation: 12707

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!

Upvotes: 1

Related Questions