Need to save a worksheet to a specific location on prompt of a save sa dialog box

AparnaWangu · Dec 19th 2018

Hi All,

I have one of the sheet named data whose data contains in the range from A1 till N100. I want to copy all the data to a new workbook as soon as the user clicks on a button.The saveas dialog box should appear which should ask me for the location and the new filename always.

Please shelp me with the code.

Regards,

Mumps · Dec 19th 2018

Does the new workbook already exist or does the macro have to create it?

AparnaWangu · Dec 19th 2018

Hi,
Macro should create the file and whenever button will be clicked save as dialog box should appear where I should mention the file name and location.

Regards,

Mumps · Dec 19th 2018

Try this macro. Please not that the new workbook will not contain the macro.

Code

Sub SaveWorkbook()
    Application.ScreenUpdating = False
    Dim fName As String
    fName = InputBox("Enter a name for the new file which is different from " & ThisWorkbook.Name)
    Sheets("Data").Copy
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            sPath = .SelectedItems(1)
            ActiveWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & fName, FileFormat:=51
            MsgBox ("The file has been saved with the name: " & fName)
            Cancel = True
        End If
    End With
End Sub

Display More

Need to save a worksheet to a specific location on prompt of a save sa dialog box

Participate now!

Share