Excel macro to always save file to My Documents

[Alwinaz]

Hi there

I am using this code to save my file by creating a foloder and then saving it with the name specified in cell A21. This works, however it saves it to source i am using it from. For example, if i try to save it from an attachment in outlook, it saves it to my outlook folder. How do i change this code to always save in in the users My Documents Folder. Here is the code i am using.

Public Sub SaveToDir()
CDir = ActiveWorkbook.Path
SaveDir = CDir & "\" & ActiveSheet.Range("A21")
'
'check to see if Dir exists if not create it. Could also abort if the Dir should exist
If Len(Dir(SaveDir, vbDirectory)) = 0 Then
   MkDir SaveDir
End If
'
'Checks to see if the Date cell is in date format
'If IsDate(ActiveSheet.Range("B1")) Then
 '   SaveName = ActiveSheet.Range("A1") & "_" & Application.Text(ActiveSheet.Range("B1"), "DD-MMM-YYYY") & ".xlsm"
'Else
    SaveName = ActiveSheet.Range("A21") & ".xlsm"
'End If
'
'Check to see if the file already exists
If Len(Dir(SaveDir & "\" & SaveName, vbDirectory)) > 0 Then
    Resp = MsgBox("File name:   " & SaveName & vbCrLf & vbCrLf & "already exists in:  " & vbCrLf & vbCrLf & SaveDir & vbCrLf & vbCrLf & "Press Okay to continue, Cancel to abort", vbOKCancel)
End If
'
If Resp = vbCancel Then
    Exit Sub
End If
Application.DisplayAlerts = False
    Sheets("Instructions").Copy
    ActiveWorkbook.SaveAs Filename:= _
        SaveDir & "\" & SaveName, FileFormat _
        :=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    
  '  ActiveSheet.Shapes("Button 1").Cut
    
    
    ActiveWindow.Close
    
MsgBox ("File name:   " & SaveName & vbCrLf & vbCrLf & "has been saved to  " & vbCrLf & vbCrLf & SaveDir)
        
999   End Sub

Thanks so long

 

[Andrew Poulsom]

Try:

CDir = CreateObject("WScript.Shell").SpecialFolders("MyDocuments")

 

[Alwinaz]

Thanks Andrew. that worked 100%

 

[Alwinaz]

Is there a reason why it only saved the one sheet and not the complete workbook?

 

[Andrew Poulsom]

You only copied one worksheet:

Sheets("Instructions").Copy

 

[Alwinaz]

how do i save the whole file then with macro's and everything?

 

[Andrew Poulsom]

Why are you copying a single worksheet? When you do that Excel adds a new workbook and that becomes the ActiveWorkbook, which is what you are saving.

 

[Alwinaz]

I dont want to copy. I want to create a folder under my documents (Funmathics) and then save the file with the file name in A21.

 

[Andrew Poulsom]

Then remove this line:

Sheets("Instructions").Copy