How to save the selected worksheet without specifying sheet name or number

Knockoutpie

Board Regular
Joined
Sep 10, 2018
Messages
116
Office Version
  1. 365
Platform
  1. Windows
Is there a way to save the active/selected worksheet without having to specify sheets(1)?

The code below is execute via command button and will take the worksheet "Quote" copy to a new workbook, and then prompt to save under the downloads directory.
I'm also trying to get that button to save whichever sheet is selected, it could be Quote or Sheet1, but not both.


VBA Code:
Private Sub CommandButton4_Click() ' save worksheet
'Gets the name of the currently visible worksheet

Filename = ActiveSheet.Name


'Puts the worksheet into its own workbook

ThisWorkbook.ActiveSheet.Copy


'Saves the workbook - uses the name of the worksheet as the name of the new workbook

'Filename = Range("A1")
'ActiveWorkbook.Save

    Dim NameFile As Variant
        With Worksheets("Quote")
        'NameFile = .Range("A1") & "_" & .Range("B5") & "_" & ".xls"
        End With
        NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Downloads\" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")
        If NameFile = False Then
        MsgBox "File not saved"
        Else
        ActiveWorkbook.SaveAs Filename:=NameFile
        End If


'Closes the newly created workbook so you are still looking at the original workbook

ActiveWorkbook.Close
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
It looks like this line has a sort of circular reference:
NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Downloads\" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")

Should likely be
NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Downloads\" & Filename, Filefilter:="Fichier Excel (*.xls), *.xls")
 
Upvote 2
Solution
It looks like this line has a sort of circular reference:
NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Downloads\" & NameFile, Filefilter:="Fichier Excel (*.xls), *.xls")

Should likely be
NameFile = Application.GetSaveAsFilename(InitialFileName:=Environ("USERPROFILE") & "\Downloads\" & Filename, Filefilter:="Fichier Excel (*.xls), *.xls")
Thanks, also changed With Worksheets to "With ActiveSheet"
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,180
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top