Quick VBA question

[STIRRELL]

Hi, I am not great at VBA and need a little help.
I am saving off a tab in a workbook with the file name based on a cell, but I want the ability to pick where to save the file.
Im not sure what to change to make that happen? Maybe a save as code? any help is appreciated.


Sheets("Monthly Summary1").Select

Rows("58:75").Select
Selection.Delete Shift:=xlUp


Dim wb As Workbook
Sheets("Monthly Summary1").Copy
Set wb = ActiveWorkbook
With wb
ActiveWorkbook.SaveAs Filename:=Sheets("Monthly Summary1").Range("B2").Value
'.Close False
End With
End Sub

 

[Micron]

One way would be to use msoFileDialogFolderPicker, or msoFileDialogFilePicker. Former is for picking folders but it doesn't show you any files, which I find kind of dumb. It doesn't provide a lot of confidence about being in the right folder at times. The latter will allow you to pick a file, which is ok if you want to over-write. You will probably get other suggestions, one of which might be to write the folder path in a cell. You can still "pick" a cell and use any of several entries that you might have but you'd need an event such as BeforeDoubleClick or something to fire the code. Then I imagine there is a Windows API for using the File Explorer dialog built into Windows.

Please paste code within code tags (use vba button on posting toolbar) to maintain indentation and readability.

 

[rlv01]

This function uses the msoFileDialogFolderPicker as @Micron mentioned in his post.

'Get the save-as folder path
Private Function GetFolder(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    
    strPath = Trim(strPath)
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a folder to save the file in"
        .AllowMultiSelect = False
        Do While Right(strPath, 1) = "\"
            strPath = Left(strPath, Len(strPath) - 1)
        Loop
        .InitialFileName = strPath & "\" '.InitialFileName always needs to end with a backslash "\"
        .ButtonName = "Select"
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
GetFolder = sItem
Debug.Print sItem
Set fldr = Nothing
End Function

Call it from your code this way.

    Dim wb As Workbook
    Dim FileName As String, FilePath As String
    FilePath = GetFolder(CurDir)                            'Curdir is just for this example, it could be some other folder.
    
    If FilePath <> "" Then
        Sheets("Monthly Summary1").Copy
        Set wb = ActiveWorkbook
        FileName = Sheets("Monthly Summary1").Range("B2").Value 'example assumes only the file name ins in cell B2. If that is not the case changes will be needed.
        FilePath = FilePath & "\" & FileName
        
        Application.DisplayAlerts = False
        wb.SaveAs FileName:=FilePath
        Application.DisplayAlerts = True
        '.Close False
    End If

 

[plettieri]

To allow the user to pick where to save the file, you can use the Application.GetSaveAsFilename method in VBA. This method opens a "Save As" dialog box, allowing the user to choose the location and file name.

try this:

Sub SaveSheetAs()
' Select the "Monthly Summary1" sheet
Sheets("Monthly Summary1").Select

' Delete rows 58 to 75
Rows("58:75").Select
Selection.Delete Shift:=xlUp

' Copy the "Monthly Summary1" sheet to a new workbook
Dim wb As Workbook
Sheets("Monthly Summary1").Copy
Set wb = ActiveWorkbook

' Get the file name from cell B2
Dim fileName As String
fileName = Sheets("Monthly Summary1").Range("B2").Value

' Open the "Save As" dialog box
Dim saveAsFileName As Variant
saveAsFileName = Application.GetSaveAsFilename(InitialFileName:=fileName, FileFilter:="Excel Files (*.xlsx), *.xlsx")

' Check if the user canceled the "Save As" dialog box
If saveAsFileName <> False Then
' Save the new workbook with the selected file name and location
wb.SaveAs Filename:=saveAsFileName, FileFormat:=xlOpenXMLWorkbook
' Optionally close the new workbook
' wb.Close False
Else
' User canceled the "Save As" dialog box
MsgBox "Save As operation canceled."
End If
End Sub

Some explaination that might be helpful​

Explanation:​

1. Selecting the Sheet and Deleting Rows:
   • The code selects the "Monthly Summary1" sheet and deletes rows 58 to 75.
2. Copying the Sheet to a New Workbook:
   • The Sheets("Monthly Summary1").Copy line creates a new workbook containing a copy of the "Monthly Summary1" sheet.
   • The Set wb = ActiveWorkbook line sets a reference to this new workbook.
3. Getting the File Name:
   • The fileName variable is set to the value from cell B2 of the "Monthly Summary1" sheet.
4. Opening the "Save As" Dialog Box:
   • The Application.GetSaveAsFilename method opens the "Save As" dialog box, with the initial file name set to the value from cell B2 and the file filter set to Excel files.
   • The saveAsFileName variable stores the file name and path selected by the user.
5. Saving the Workbook:
   • If the user selects a file name and location, the workbook is saved using the wb.SaveAs method.
   • If the user cancels the "Save As" dialog box, a message box informs them that the operation was canceled.

This code provides a user-friendly way to save the file, allowing the user to choose the location and file name through the "Save As" dialog box.


Hope this helps
plettieri

 

[rlv01]

FWIW: How to use code tags when posting code.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com