Macro Error On Error Resume not working with save file error

[NormChart55]

Hello All,

I have been trying to get this working for quite a bit and no matter what variation I try I always get the Visual basic run time error prompt. I am trying to avoid that as the template I am updating will be used my many people and trying to avoid issues related to that.

Below is the code I am using. its a simple copy/paste into new sheet and saving as a file name. The problem is that when there is already a file at the specified location and a user chooses to hit No or Cancel the run time error occurs. I have tried doing On Error Resume Next but it never passes the save line and current code is below where I am trying to get it to just choose the cell range again if it errors out. I have seen a few posts around this and cannot get any of the code to work when trying to get it into my template. Any thoughts on this are certainly appreciated.

ActiveSheet.Range("A1:F15000").Copy
  Set NewBook = Workbooks.Add
  NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
   
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    ActiveWindow.DisplayGridlines = False

    Columns("A:F").Select
    Columns("A:F").EntireColumn.AutoFit

    Range("A1").Select
   
On Error GoTo err_handler
 ActiveWorkbook.SaveAs Filename:=Path & Filename & " " & dt

err_handler:
Range("A1").Select

 

[NormChart55]

Thank you. I have tried a variation of that and still get the same message. The code I used is below and still unfortunately provides the same message to end or debug.

Sub SaveRangeAsNewFileUpload()
Dim Path As String
Dim Filename As String

    Path = "C:\Upload\"
    Filename = "Upload"

Dim dt As String
    dt = Format(CStr(Now), "mm dd yyyy")

    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=RC[8]"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=RC[14]"
    Range("C2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=RC[18]"
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "=RC[18]"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=RC[18]"
    Range("G2").Select

   On Error GoTo iError
 
    Range("A2:F2").Select
    Selection.AutoFill Destination:=Range("A2:F" & Range("G" & Rows.Count).End(xlUp).Row)
 
iError:

ActiveSheet.Range("A1:F15000").Copy
  Set NewBook = Workbooks.Add
  NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
          :=False, Transpose:=False
  
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
        .FreezePanes = True
        .DisplayGridlines = False
    End With

    Columns("A:F").Select
    Columns("A:F").EntireColumn.AutoFit

    Range("A1").Select
  
On Error Resume Next
  ActiveWorkbook.SaveAs Filename:=Path & Filename & " " & dt

On Error GoTo 0

End Sub

The plot thickens. This works and does not cause the error to pop up if there are more than 1 line (plus the header line) that was trying to be saved. if its only the header and 1 other line then the message comes back for me to end or debug, but any other combination of lines and that does not occur? only 2 rows, message received. More than 2 rows then you can hit either cancel or no and it goes on.

 

[Micron]

I do not have that on my modules

I can only reply based on the code you post, which was in post 6:

If Err.Number = 1004 Then
Workbooks(strNewFile).Close False
Resume exitHere
End If

Have to go out for a while; will try to get back to it later.

 

[Micron]

somehow, we are talking about 2 different error messages that have the same number. This is what I'm getting:
?err.Number
1004
?err.Description
Application-defined or object-defined error

As noted, have to drop for now.

 

[NormChart55]

Thank you all for your assistance! I have this worked out now. The issue was with my code on this section. Changing this to also On Error Resume next now has this working exactly as intended. much appreciated to everybody who has responded.

   On Error GoTo iError
 
    Range("A2:F2").Select
    Selection.AutoFill Destination:=Range("A2:F" & Range("G" & Rows.Count).End(xlUp).Row)
 
iError:

 

[Micron]

Nice to see you figured it out, but seldom would I use that as a solution. One day the error may be for some other reason than what you coded that for and it will attempt to continue regardless. Better to trap errors and handle them IMHO.