VBA Help

[excel_learnerz]

Hello amazing people,

I was wondering can you help me with the below

This is a current macro that I am using that copies everything (format, tab names, add data) and doesn't have formulas, removes the macros from the file also, works wonderfully, but is there a way to save the new excel into the same location automatically instead of manually saving it

Sub formula_removal()
Dim anySheet As Worksheet
ActiveWorkbook.Sheets.Copy
For Each anySheet In ActiveWorkbook.Worksheets
anySheet.UsedRange.Copy
anySheet.UsedRange.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Next
Application.DisplayAlerts = False
Sheets("sheet4").Delete
Sheets("sheet5").Delete
Sheets("sheet6").Delete
Sheets("sheet7").Delete
Sheets("sheet8").Delete
Sheets("sheet9").Delete
Sheets("sheet10").Delete
Sheets("sheet11").Delete
Application.DisplayAlerts = True

End Sub


Thanks

 

[excel_learnerz]

Would anyone be able to help with this please?

 

[ScottR]

Do you mean?

Activeworkbook.save

 

[gallen]

This is untested but might point you in the right direction:

Sub formula_removal()
Dim anySheet As Worksheet
Dim sPath As String 'path of current workbook
Dim sSaveAs As String 'file name to save as


    sPath = ThisWorkbook.Path & "\" 'path of current book
    sSaveAs = "YOURFILENAME" 'change as required
    
    ActiveWorkbook.Sheets.Copy
    ActiveWorkbook.SaveAs Filename:=sPath & sSaveAs, FileFormat:=xlOpenXMLWorkbook
    For Each anySheet In ActiveWorkbook.Worksheets
    anySheet.UsedRange.Copy
    anySheet.UsedRange.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Next
    Application.DisplayAlerts = False
    Sheets("sheet4").Delete
    Sheets("sheet5").Delete
    Sheets("sheet6").Delete
    Sheets("sheet7").Delete
    Sheets("sheet8").Delete
    Sheets("sheet9").Delete
    Sheets("sheet10").Delete
    Sheets("sheet11").Delete
    Application.DisplayAlerts = True


End Sub

 

[excel_learnerz]

Gallen and ScottR, thanks, got it working like a charm now