I finally gave in and found this forum. I am so close to what I want but just can't get it right. I currently have the following code linked to a button and it works perfectly for saving my macro enabled workbook as a regular workbook in the chosen location.
Sub SaveAsCathyII() Dim path As String Dim filename1 As String path = "D:\imgsta x\2020\FAKENAME\ORDER COPIES\" filename1 = Range("AB54").Text Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=path & filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook Application.DisplayAlerts = True End Sub
HOWEVER, I'd prefer a "save as" dialog box open and then I click save manually, the above code just saves the file. I found the following code that gets the filename correct and opens the save as dialog box but it does NOT include a predefined path and it also opens the save as dialog with Macro Enabled workbook selected:
Sub TEST() Dim FileName As String FileName = Range("AB54").Value Dim fPth As Object Set fPth = Application.FileDialog(msoFileDialogSaveAs) With fPth .InitialFileName = FileName .Title = "Save your File" .FilterIndex = 2 .InitialView = msoFileDialogViewList If .Show <> 0 Then ThisWorkbook.SaveAs FileName:=.SelectedItems(1), FileFormat:=xlOpenXMLWorkbook End If End With End Sub
So, I need a combination of the 2 above examples. I'd like the macro to choose a cell that prefills the filename (that works in both the above), have a predefined path in the code where the files saves to (that works in the first code but not the second), have a save as dialog box open showing the filename prefilled (that works in the second code but it doesn't choose "Excel WorkbooK" as the file type or have a predefined path). I need the macro enabled workbook to save as a regular workbook, i.e. have "Excel Workbook" chosen by default in the save as dialog box file type but with the dropdown of all file types available (the dropdown of all file types is available in the second example). Once its filled out and ready to save, there is no need for the macros to be enabled anymore and frankly they need to be disabled so the next person who views the workbook doesn't make undesirable changes.
I hope this makes sense. The first bit of code accomplishes most of what I want, it just needs the save as dialog box vs just saving to the chosen path.
Thanks in advance for any help!