Hi,
I'm trying to figure out how to have VBA check the path before it saves and it does not match, then a pop up box opens and the user can select the drive and folder where they want the file saved.
Right now I have the location hard coded with the path to my C drive. Now several people need to use this VBA code and I don't want them to go into the VBA to change the path.
Any help would be greatly appreciated.
Here is my code
Sub MakeNewFile()
Dim FName As String
Dim ShtName As String
Dim FPath As String
Dim NewBook As Workbook
Dim TDate As String
Application.ScreenUpdating = False
FPath = "C:\Users\myuserID\Desktop"
Range("A2:O100").Copy
Set NewBook = Workbooks.Add
Sheets.Add.Name = Format(Date, "mm-dd-yy")
With NewBook.Sheets(1).Range("A2:O100")
ActiveSheet.Paste
Cells.Select
Range("A2").Activate
Cells.EntireColumn.AutoFit
Cells.Select
ActiveWindow.Zoom = 75
ActiveWorkbook.SaveAs Filename:=FPath & "\" & "MS_Vetting_Request_" & Format(CStr(Now()), "mm.dd.yy") & ".xlsx"
ActiveWorkbook.Close SaveChanges:=True
Application.ScreenUpdating = True
MsgBox "Exported to Desktop"
End With
End Sub
Bookmarks