Macro to save and replace without prompt

[Msgjazz]

I have searched and tried all the results from google but not luck.

I want the macro to save the file AND replace the current file AND not prompt me to save. I have all but the last part.

Sub OnBaseAutoFillPrep()
'
' OnBaseAutoFillPrep Macro
'
Dim i As Double
For i = 27 To 1000000
If Cells(i, 1).Value = Empty Then Exit For
Next
'
Columns("F:I").Select
Selection.Replace What:="-*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Columns("J:J").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("L26").Select
Selection.AutoFill Destination:=Range("L26:L" & i - 1)
Range("L27:L" & i - 1).Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Rows("2:25").Select
Range("A25").Activate
Selection.Delete Shift:=xlUp
Range("A2").Select
ChDir "G:\Monthly Reports\Monthly Jul 2021 - Jun 2022\Templates"
ActiveWorkbook.SaveAs Filename:= _
"G:\Monthly Reports\Monthly Jul 2021 - Jun 2022\Templates\OnBase AutoFill FlatFile.xlsm" _
, CreateBackup:=True

Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS

Application.Run "'OnBase AutoFill FlatFile.xlsm'!OutputQuotedCSV"
End Sub

Would appreciate any help.

Thanks

 

[igold]

You have this line at the bottom of your code:

Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS

but I don't see the line of code that would turn DisplayAlerts Off anywhere. It should be placed somewhere above your SaveAs line

Application.DisplayAlerts = False

 

[Msgjazz]

You have this line at the bottom of your code:

Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS

but I don't see the line of code that would turn DisplayAlerts Off anywhere. It should be placed somewhere above your SaveAs line

Application.DisplayAlerts = False

Thanks but still getting the popup

Would like it to replace the file with asking to.

 

[igold]

I don't see your code. Did you add the line to turn display alerts off.

 

[Msgjazz]

Sorry....

Sub OnBaseAutoFillPrep()
'
' OnBaseAutoFillPrep Macro
'
Dim i As Double
For i = 27 To 1000000
If Cells(i, 1).Value = Empty Then Exit For
Next
'
Columns("F:I").Select
Selection.Replace What:="-*", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Columns("J:J").Select
Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("L26").Select
Selection.AutoFill Destination:=Range("L26:L" & i - 1)
Range("L27:L" & i - 1).Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("K:K").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Rows("2:25").Select
Range("A25").Activate
Selection.Delete Shift:=xlUp
Range("A2").Select
ChDir "G:\Monthly Reports\Monthly Jul 2021 - Jun 2022\Templates"
ActiveWorkbook.SaveAs Filename:= _
"G:\Monthly Reports\Monthly Jul 2021 - Jun 2022\Templates\OnBase AutoFill FlatFile.xlsm" _
, CreateBackup:=True
Application.DisplayAlerts = False
'Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS

Application.Run "'OnBase AutoFill FlatFile.xlsm'!OutputQuotedCSV"
End Sub
- - - - - - -

Still getting

I also ran it by including the line
Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS

and excluding the same line by adding the ( ' ) as
'Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS

Thank you for taking the time.

 

[igold]

This line

Application.DisplayAlerts = False

must be above this line in the code

ActiveWorkbook.SaveAs Filename:= _
"G:\Monthly Reports\Monthly Jul 2021 - Jun 2022\Templates\OnBase AutoFill FlatFile.xlsm" _
, CreateBackup:=True

or when the code executes the SaveAs line DisplayAlerts is still True or active. Try making it the first line of your code right after your declaration

Dim i As Double

Sub OnBaseAutoFillPrep()
'
' OnBaseAutoFillPrep Macro
'
    Dim i As Double

    Application.DisplayAlerts = False
    For i = 27 To 1000000
    If Cells(i, 1).Value = Empty Then Exit For
    Next
    '
    Columns("F:I").Select
    Selection.Replace What:="-*", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder _
    :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("J:J").Select
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Range("L26").Select
    Selection.AutoFill Destination:=Range("L26:L" & i - 1)
    Range("L27:L" & i - 1).Select
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Columns("K:K").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Rows("2:25").Select
    Range("A25").Activate
    Selection.Delete Shift:=xlUp
    Range("A2").Select
    ChDir "G:\Monthly Reports\Monthly Jul 2021 - Jun 2022\Templates"
    ActiveWorkbook.SaveAs Filename:= _
    "G:\Monthly Reports\Monthly Jul 2021 - Jun 2022\Templates\OnBase AutoFill FlatFile.xlsm" _
    , CreateBackup:=True
    
    Application.DisplayAlerts = True 'RESETS DISPLAY ALERTS
    Application.Run "'OnBase AutoFill FlatFile.xlsm'!OutputQuotedCSV"
End Sub

 

[Msgjazz]

Thank you. That did work. Sorry for the delay, but I've been laid up with back trouble. It works so good now that I had to add a message box to let me know it's finish. Thanks again for the help

 

[igold]

You're welcome. I was happy to help. Thanks for the feedback.

I hope your back is better, back pain can be a *****. Hang in there.

If a code a code provided has answered your question please consider marking it as solved to help other users.