So far I have the following code that asks for a file name, adds the date after the user-entered file name and saves a xls file in the paths of the main file. It works, but I would like to get a dialog box to change the path of the saved file. It would be great if the user-entered file name and date can be pre-positioned in the file name box. What would I need to add to the code to get it to do this?
Private Sub CommandButton3_Click() '===============SAVE DATA TO UPLOAD FILE================ Dim ws As Worksheet Dim MyDay As String Dim MyMonth As String Dim MyYear As String Dim MyPath As String Dim MyFileName As String Dim MyCellContent As String Dim LR As Integer Dim test Sheets("Upload List").Select LR = Worksheets("Upload List").Cells(Rows.count, 1).End(xlUp).Row + 1 If LR = 2 Then MsgBox ("Nothing to save") Sheets("Opening Sheet").Select Exit Sub End If MyDay = Day(Now) MyMonth = Month(Now) MyYear = Year(Now) MyPath = ActiveWorkbook.path MyCellContent = InputBox("Upload file name", "Upload File Name") Set ws = ActiveSheet MyFileName = MyCellContent & "_" & MyMonth & "." & MyDay & "." & MyYear & ".xls" ws.Copy ChDir MyPath Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:= _ MyFileName, FileFormat:=xlExcel8, password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.DisplayAlerts = True ActiveWorkbook.Close Sheets("Opening sheet").Select MsgBox ("Upload file created named: " & MyFileName) End Sub