Save As button to target Desktop

    I have an output worksheet populated via UserForms and I'm using this simple button vba to copy and save the worksheet. It works fine apart from the fact that it randomly selects my Pictures folder to save in. How can I amend the code to always save in Desktop.


    Woz (Using Excel 2011 for mac)

    Code
    Sub ButtonSaveAs_Click()
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=Range("B2")
End Sub

    To make sure that you always save to the desktop place this code into a standard module


    Code
    Public Function GetDesktop() As String
GetDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & _
Application.PathSeparator
End Function

    Then amend you button code to this, I assume the name of the folder is in B2


    Code
    Sub ButtonSaveAs_Click()
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=GetDesktop & Range("B2")
End Sub

    I've used this Roy but it keeps falling over on...

    1. GetDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & _
    2. Application.PathSeparator

    Is it a windows v mac thing?

    Woz

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login