CODEWITHSUNDEEP
excelvba
Abhijeet
Abhijeet

Reputation: 269

How to save current sheet as workbook on desktop with date and Time in vba

I want to save current sheet as separate workbook on desktop with date and time in new workbook name.

How to save it?

Upvotes: 0

Views: 3395

Answers (1)

Abhijeet
Abhijeet

Reputation: 269

We can use below code to export current sheet to desktop as workbook with date and time in name.

Sub Expo()

    Application.ScreenUpdating = False

    'Get path for desktop of user PC
    Path = Environ("USERPROFILE") & "\Desktop"
    Sheet1.Cells.Copy

    'Create new workbook and past copied data in new workbook & save to desktop
    Workbooks.Add (xlWBATWorksheet)
    ActiveWorkbook.ActiveSheet.Paste
    ActiveWorkbook.ActiveSheet.Name = "report"
    ActiveWorkbook.SaveAs Filename:=Path & "\" & "report " & Format(CStr(Now()), "dd-mmm (hh.mm.ss AM/PM)") & ".xlsx"
    ActiveWorkbook.Close SaveChanges:=True

    Application.ScreenUpdating = True

    MsgBox "Exported to Desktop"

End Sub

Upvotes: 1

Related Questions