Excel VBA to export and save sheet as a new workbook that new workbook's name contains current date or time without formulas,.

[Raastaa]

I need a VBA code that helps me export an excel sheet and save it as an excel workbook without formulas,
and Name of new exported workbook should be contained old workbook name + current date or time.

 

[shinigamilight]

Try this one

Sub WorkBook_New()
        Dim wk As Worksheet
        Dim oldWk As Workbook
        Set oldWk = ActiveWorkbook
        Dim WKName As String
        Dim SaveName As String
        Dim FilePath As String
        
        FilePath = "C:\Users\Windows\Desktop\"                                '  change file save path
        
        WKName = WorksheetFunction.Replace(oldWk.Name, InStr(1, oldWk.Name, ".x"), 95, "")
        SaveDes = FilePath & WKName & " " & Format(Now, "dd-mmm-yy HH,MM") & " hrs" & ".xlsx"
        oldWk.ActiveSheet.UsedRange.Copy
        

        Set wk = Workbooks.Add.Sheets(1)
        wk.Range("A1").PasteSpecial xlPasteValues
        wk.SaveAs SaveDes, 51
        
End Sub

 

[HaHoBe]

Hii Raastaa,

what about this for a start:

Public Sub MrE_1227869_170180F()
' https://www.mrexcel.com/board/threads/excel-vba-to-export-and-save-sheet-as-a-new-workbook-that-new-workbooks-name-contains-current-date-or-time-without-formulas.1227869/
' Created: 20230124
' By:      HaHoBe
Dim wbThis As Workbook

Set wbThis = ThisWorkbook
wbThis.ActiveSheet.Copy
With ActiveWorkbook
  With .ActiveSheet.UsedRange
    .Value = .Value
  End With
  .SaveAs wbThis.Path & Application.PathSeparator & Left(wbThis.Name, InStrRev(wbThis.Name, ".") - 1) & Format(Now, "_yymmdd_hhmmss") & _
        ".xlsx", FileFormat:=51
  .Close False
End With
Set wbThis = Nothing
End Sub

It takes the ActiveSheet from the workbook with code and saves to folder of workbook with code as macrofree workbook.

Ciao,
Holger