Macro to save file as Xlsx

JohnDrew · Nov 16th 2017

I need a macro to copy and paste all values in UPLOAD sheet, then delete the DATA sheet and save as a xlsx file using "SQUARE" and current date to DESKTOP

Mumps · Nov 16th 2017

Try:

Code

Sub JohnDrew()
    Application.ScreenUpdating = False
    Dim DesktopAddress As String
    DesktopAddress = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    Dim LastRow As Long
    LastRow = Sheets("UPLOAD").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("UPLOAD").Range("A2:B" & LastRow).Copy
    Sheets("UPLOAD").Cells(2, 1).PasteSpecial xlPasteValues
    Application.DisplayAlerts = False
    Sheets("DATA").Delete
    ChDir DesktopAddress
    ActiveWorkbook.SaveAs Filename:=DesktopAddress & "\" & "SQUARE" & Replace(Date, "/", "-") & ".xlsx", FileFormat:=51
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Display More

Macro to save file as Xlsx

Participate now!

Share