VBA Save as new workbook Use MACRO ON NEW workbook?

[Melimob]

Hi All

Not sure if this can be done?

Ultimately I want to create a new workbook without formulas with a part dynamic file name but the user selects the folder.

I was thinking I could:

a) SaveAs 'File name would read a cell +todays date'.
b) user chooses path
c) saves
d) in newly saved wkbook? copy and pastes over itself to remove formulas
e) deletes unwanted sheets
f) saves again

The code I have for this is below but I didn't know how to do a) from above?
also, it's not saving as a new file it just writes over this one?

I guess I should also add a box in case the file already exists it asks if they want to overwrite?

Sub SaveAsNewWkbook_New()
    
    RemoveFilters1a
        
    Application.GetSaveAsFilename ActiveWorkbook.Path
    
    ActiveWorkbook.Save
    
    All_Cells_In_All_WorkSheets_1
    ActiveWorkbook.Save
End Sub

RemoveFilters1a code:

Sub RemoveFilters1a()


    Dim WS As Worksheet


    With Application


        .EnableEvents = False
        .ScreenUpdating = False




        For Each WS In ActiveWorkbook.Sheets


            On Error Resume Next


            WS.ShowAllData


            On Error GoTo 0


        Next


        .EnableEvents = True
        .ScreenUpdating = True


    End With


End Sub

All_Cells_In_All_WorkSheets_1 code:

Sub All_Cells_In_All_WorkSheets_1()
    Dim sh As Worksheet
    
    Sheets("ImportNonCCB").Visible = True
    Sheets("LKUPs").Visible = True
    
    For Each sh In ActiveWorkbook.Worksheets
        sh.Select
        With sh.UsedRange
            .Cells.Copy
            .Cells.PasteSpecial xlPasteValues
            .Cells(1).Select
        End With
        Application.CutCopyMode = False
    Next sh
    
    Sheets("CC Reconfiguration Data").Range("I3" & LastRow) _
    .Formula = "=VLOOKUP(tblCCReconfig[Control Centre Company Builds],Table6[[Control Centre Company Builds]:[POA GDS]],6,0)"
    
    Sheets("ImportNonCCB").Visible = False
    Sheets("ImportCCBMI").Visible = False
    Sheets("ImportCCB").Visible = False
    Sheets("LKUPs").Visible = False
    Sheets("Instructions").Delete


    
End Sub

any advice gratefully received...this is the final piece of this project and I am done! woo hoo!

 

[Melimob]

To save a macro enabled workbook (.xlsm) as a macrodisabled workbook (.xlsx)

Dim myPath As String
myPath = "C:\DiffDir\"  'You only need this if the path is different than your default path.
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs myPath & "newFileName.xlsx", FileFormat:=51
Application.DisplayAlerts = True

The Display alerts bit will prevent the message telling you that you will lose your macros. See "Workbook.SaveAs Method" in VBA help file.

thanks again for this!