Save As button using VBA Code

[gary2014]

Hi,
I have created 50 excel sheet in which I have set 4 Buttons in each sheet.
1. Reset
2. Korfball
3. Sepaktakraw
4. Kabbadi

Save Button.jpg
Now I want to add another button "Save as" in all 50 sheets.

Once the user clicks on the "Save as" button in one sheet, only that particular sheet should be saved on desktop or in G Drive by name "MCR" and date.

Please help me with this.

[mperrah]

See if this is close to what you are looking for.

Sub vba52280()
Dim preName As String, sPath As String, ThisIsNow As String, sFileName As String

    preName = "MCR"
    sPath = "G:\"
    ThisIsNow = Format(Now(), "yyyymmdd")
    sFileName = preName & ThisIsNow & ".xlsm"
    MsgBox sFileName ' can be omitted
    
    With ActiveSheet
    .Select
    .Copy
    ActiveWorkbook.SaveAs sPath & sFileName, FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
    End With
End Sub

-mark
(nod to Yongle for source)

[gary2014]

Hi,
Many thanks for the reply. I need little more of your guidance in this regard.

I am not sure if I am doing it correct. Let put it in a step by step manner of what I did.

Step 1: In the excel sheet, I clicked on Developer tab > Insert > Command Button.
Example 1.jpg




Step 2: Renamed the command button as "SaveAs".
Step 3: Right click on "SaveAs" button and then click on View Code.
(The following appears)

Example 2.jpg

Please guide me if I am doing it incorrect.

Thank you.

[Yongle]

That looks correct. Just put the code in below the line beginning Private Sub (do not change the name of the sub)

[gary2014]

Hi,
It gives an error which is as follows:

Example 3.jpg

[Yongle]

Just delete line 2 (beginning with Sub VBA...)

[gary2014]

Excellent!
Its working now.
Thank you very much.

[Yongle]

Great news - please click on Thread Tools (at top of thread) and mark the thread as "Solved"