How to use VBA to save a file to desktop regardless of user?

[Indominus]

Hello. I am trying to add this to a longer VBA code I have. I need two separate codes. Both of them I need Excel to save the active workbook with the file name (one being Pickorder1 and one being Pickorder2) along with today’s date. So save it to the Desktop regardless of user as this will be used by many. As CSV format. So the end result for one would be Pickorder1 31-Aug-2020.csv. Then similar with the other. So I just need the name of the file and today’s date. Thank you to anyone willing to help.

 

[mrshl9898]

See if this does the job.

Sub seetest()

Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
newfilename = Left(Filename, InStr(Filename, ".") - 1)

ThisWorkbook.SaveAs Filename:=desktop & newfilename & " " & today

End Sub

 

[Indominus]

See if this does the job.

Sub seetest()

user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop"

End Sub

Thank you. So first off the user name is going to change. This file with the code will be sent around to others. Also this line of code wouldn’t save it correct?

 

[mrshl9898]

Try my edited post. Thought you just wanted the desktop part initially

Environ("Username") finds the users username

 

[Indominus]

Try my edited post. Thought you just wanted the desktop part initially

Environ("Username") finds the users username

So it saves it. However it keeps in the original name also. How can I name it exactly what I want in addition to the current date?

 

[mrshl9898]

Do you want to hard code the name?

Sub seetest()

Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
newfilename = Left(Filename, InStr(Filename, ".") - 1)

ThisWorkbook.SaveAs Filename:=desktop & "exactly what I want " & today

End Sub

 

[Indominus]

Do you want to hard code the name?

Sub seetest()

Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
newfilename = Left(Filename, InStr(Filename, ".") - 1)

ThisWorkbook.SaveAs Filename:=desktop & "exactly what I want " & today

End Sub

Thank you this works. Last question. So this code will go into one workbook. Referenced as Checklist. However the workbook it will save is another one (titled Pickorder). I have a code that activates the open Pickorder workbook, however running this code after does not recognize that one has the active one. It saves the Checklist workbook (with the code built in). Is there a way change this code to identify the open workbook with a title such as Pickorder? Here is the activation code I have that doesn’t work with this. This activation code works in other codes by the way.

    For Each w In Workbooks
    If UCase(w.Name) Like UCase("*Pick*order*") Then
    Windows(w.Name).Activate
    Exit For
    End If
    Next w

 

[mrshl9898]

To incorporate your code, try:

Sub seetest()

Dim wb As Workbook
    
Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
newfilename = Left(Filename, InStr(Filename, ".") - 1)

    For Each w In Workbooks
    If UCase(w.Name) Like UCase("*Pick*order*") Then
    Windows(w.Name).Activate
    Set wb = ActiveWorkbook
    Exit For
    End If
    Next w

wb.SaveAs Filename:=desktop & "exactly what I want " & today

End Sub

 

[Indominus]

To incorporate your code, try:

Sub seetest()

Dim wb As Workbook
   
Filename = ActiveWorkbook.Name
user = Environ("Username")
desktop = "C:\Users\" & user & "\Desktop\"
today = Format(Now(), "DD-Mmm-YYYY")
newfilename = Left(Filename, InStr(Filename, ".") - 1)

    For Each w In Workbooks
    If UCase(w.Name) Like UCase("*Pick*order*") Then
    Windows(w.Name).Activate
    Set wb = ActiveWorkbook
    Exit For
    End If
    Next w

wb.SaveAs Filename:=desktop & "exactly what I want " & today

End Sub

It works perfectly!!! Thank you so much. It is very much appreciated. I was researching for awhile and couldn’t find anything like what I needed.