Save a File to the Destop with VBA

The following is a simple example of copying data from the current workbook and saving it to the desktop.  The procedure will then save the file and close it leaving the user on the original sheet.

Sub SavetoDesktop() 'Save an Excel file to the desktop with the path hard coded.
Range("A1", Range("H" & Rows.Count).End(xlUp)).Copy
Workbooks.Add
[a1].PasteSpecial xlValues
ActiveWorkbook.SaveAs "C:\Users\HYMC\Desktop\XLName.xls" 'ChngeExcel name to variable
ActiveWorkbook.Close False 'don't save see line above.
End Sub


The above is the address of the desktop on my computer;

"C:\Users\HYMC\Desktop\XLName.xls"

So will need to be altered to work on your computer.


Dynamic Desktop Capture


To make the above code more dynamic we can trap the desktop path. The following will save the active workbook to the Desktop.

Sub SavetoDesktop2() 'Excel VBA to save a file to the desktop with a dynamic Path.
Dim Path As String
Range("A1", Range("H" & Rows.Count).End(xlUp)).Copy
Workbooks.Add
[a1].PasteSpecial xlValues
Path=Createobject("WScript.Shell").SpecialFolders("Desktop") & "\"
ActiveWorkbook.SaveAs Path & "XL File.xls"
End Sub


It is an improvement as all you really need to worry about now is the XL file name.