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

 

[Micron]

Are you sure you're getting an error and not just a prompt to overwrite (or not) the existing workbook? The button click you refer to is the dialog for overwriting or not? I think in that case, cancel is error 1004. At present I have no idea how to trap either of the other results if you need to. You probably should test for the existence of a file rather than deal with the prompt because at that point, you now have a new unsaved workbook open.

Anyway, your error goto needs to be at the top to direct flow to your error handler, which should be at the bottom. At the end of the code for this operation, you probably should have an exit line label(e.g. exitHere: ), followed by an Exit Sub (or function?) line. If all goes well, it exits at that line. If not, the error handler should be used to deal with any errors you can foresee happening. A Select Case block is often used for this. You then resume as appropriate for each error. If that is to stop the procedure, then you
Resume exitHere
where you also would do any cleanup (e.g. setting objects to Nothing).
I tried a modified version of your code but didn't get any error message. My answer might be a bit ambiguous since there's no indication of what your path variables contain and how they get their values.

 

[NormChart55]

Are you sure you're getting an error and not just a prompt to overwrite (or not) the existing workbook? You're not showing all of the relevant code and what's in the button click is likely relevant.

Anyway, your error goto needs to be at the top to direct flow to your error handler, which should be at the bottom. At the end of the code for this operation, you probably should have an exit line label(e.g. exitHere: ), followed by an Exit Sub (or function?) line. If all goes well, it exits at that line. If not, the error handler should be used to deal with any errors you can foresee happening. A Select Case block is often used for this. You then resume as appropriate for each error. If that is to stop the procedure, then you
Resume exitHere
where you also would do any cleanup (e.g. setting objects to Nothing).
I tried a modified version of your code but didn't get any error message.

Thank you for your reply and assistance. I do get the prompt which asks if I want to replace the current file. Choosing 'No' or 'Cancel" then gives me the run-time error '1004': Method 'SaveAs' of object'_Workbook failed which requires me to end or debug. That is what I was referring to as the error. If I debug the save as line is highlighted as to what failed and that is what I cannot seem to get passed. What I am trying to do is get the sub to end there or get it to close the file that was just created or something similar to just resume on and end.

 

[NormChart55]

Thank you for your reply and assistance. I do get the prompt which asks if I want to replace the current file. Choosing 'No' or 'Cancel" then gives me the run-time error '1004': Method 'SaveAs' of object'_Workbook failed which requires me to end or debug. That is what I was referring to as the error. If I debug the save as line is highlighted as to what failed and that is what I cannot seem to get passed. What I am trying to do is get the sub to end there or get it to close the file that was just created or something similar to just resume on and end.

Full Code

Sub SaveRangeAsNewFileRelianceUpload()
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
    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

 
End Sub

 

[Micron]

Based on your first code post, how about

Dim NewBook As Workbook
Dim Path As String, FileName As String, strNewFile As String

On Error GoTo errHandler

ActiveSheet.Range("A1:F15000").Copy
ActiveSheet.Range("A1:F10").Copy
Set NewBook = Workbooks.Add
NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

strNewFile = NewBook.Name

With ActiveWindow
   .SplitColumn = 0
   .SplitRow = 1
   .FreezePanes = True
   .DisplayGridlines = False
End With

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

Range("A1").Select

ActiveWorkbook.SaveAs FileName:=Path & FileName & " " & dt

exitHere:
Exit Sub

errHandler:

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

I had to modify somewhat so I could test. Hopefully I put everything back. I also modified your With block, not for testing but for consolidation.
Ought to be possible to have Range("E2").FormulaR1C1 = "=RC[18]" etc. without selecting first?

 

[NormChart55]

Based on your first code post, how about

Dim NewBook As Workbook
Dim Path As String, FileName As String, strNewFile As String

On Error GoTo errHandler

ActiveSheet.Range("A1:F15000").Copy
ActiveSheet.Range("A1:F10").Copy
Set NewBook = Workbooks.Add
NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

strNewFile = NewBook.Name

With ActiveWindow
   .SplitColumn = 0
   .SplitRow = 1
   .FreezePanes = True
   .DisplayGridlines = False
End With

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

Range("A1").Select

ActiveWorkbook.SaveAs FileName:=Path & FileName & " " & dt

exitHere:
Exit Sub

errHandler:

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

I had to modify somewhat so I could test. Hopefully I put everything back. I also modified your With block, not for testing but for consolidation.
Ought to be possible to have Range("E2").FormulaR1C1 = "=RC[18]" etc. without selecting first?

Once again thank you for your time and effort. I made some adjustments. I added the on Error GoTo errHandler at the top, changed the With Block and included the errHandler adjustments. I do still receive the same error like it cannot account for the clicks on 'no' or 'cancel'.

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

On Error GoTo errHandler

    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
    
  ActiveWorkbook.SaveAs Filename:=Path & Filename & " " & dt

exitHere:
Exit Sub

errHandler:

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

 
End Sub

 

[Micron]

you forgot to declare strNewFile.
Works for me when Cancel or No is clicked (either choice raises error 1004)
If yes option requires capturing, I don't know how because it doesn't raise an error.
You don't have Option Explicit at the top of all your code modules by default, do you? It has been said that if one fails to set that in vb editor options, one deserves all the coding grief that they get.

 

[Grasor]

Sub SaveAsTest()

On Error Resume Next
ActiveWorkbook.SaveAs Filename:=Path & Filename & " " & dt

On Error Goto 0

End Sub

You can remove the error handler portion when handling simple errors like this where you just want the code to continue.

The "On Error Goto 0" statement sets the reserved Err keyword status to 0. I don't always use this, but it's helpful to remember that just using Resume Next doesn't clear the error status of 1004 in this case, it just ignores it. You can print out the error status using Debug.Print Err in your code to see what error is active.

 

[NormChart55]

you forgot to declare strNewFile.
Works for me when Cancel or No is clicked (either choice raises error 1004)
If yes option requires capturing, I don't know how because it doesn't raise an error.
You don't have Option Explicit at the top of all your code modules by default, do you? It has been said that if one fails to set that in vb editor options, one deserves all the coding grief that they get.

I do not have that on my modules - below my current full code is attached (as it stands now) which does have the file name at the top. I did test with it added at the same spot based on your example and I unfortunately received the same problem.

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
    
  ActiveWorkbook.SaveAs Filename:=Path & Filename & " " & dt

exitHere:
Exit Sub

errHandler:

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

 
End Sub

 

[NormChart55]

Sub SaveAsTest()

On Error Resume Next
ActiveWorkbook.SaveAs Filename:=Path & Filename & " " & dt

On Error Goto 0

End Sub

You can remove the error handler portion when handling simple errors like this where you just want the code to continue.

The "On Error Goto 0" statement sets the reserved Err keyword status to 0. I don't always use this, but it's helpful to remember that just using Resume Next doesn't clear the error status of 1004 in this case, it just ignores it. You can print out the error status using Debug.Print Err in your code to see what error is active.

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