personal macro workbook - save as

[rikvny02]

Thanks in advance for your help

I had to move my macros into a personal macro workbook and now the SAVE AS macro will not work. I have several macros that i use the call function with and the last step is to save the workbook. Now that the macros are in the personal macro workbook the save as macro saves everything as "PERSONAL.XLSB". The goal would be to save the active file with its current name. ie - Rikki_ws

Current code is as follows

Sub SaveWorkbook()
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
sPath = .SelectedItems(1)
ThisWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & "Copy of " & ThisWorkbook.Name
Cancel = True
End If
End With
End Sub

 

[Jaafar Tribak]

ActiveWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & "Copy of " & ActiveWorkbook.Name

Or a better alternative (In case you want to save a non-active workbook ), add a workbook argument to the SUB:

Sub SaveWorkbook(ByVal oWorkbook As Workbook)
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            sPath = .SelectedItems(1)
            oWorkbook.SaveAs Filename:=sPath & Application.PathSeparator & "Copy of " & oWorkbook.Name
            Cancel = True
        End If
    End With
End Sub

And then you pass the workbook you want to save in the argument when calling the SUB as follows:

SaveWorkbook ThisWorkbook

 

[rikvny02]

Unfortunately, I get the same result. More importantly what's saved is not my workbook at all. Please see below and thank you for your time and effort.

Im looking for it to save like the below example

 

[Jaafar Tribak]

Ther must be something you are doing. The code I posted should save the active workbook or the workbook you are calling the SaveWorkbook SUB from.
Is the personal .xlsb visible and\or active when you call the code ?

 

[rikvny02]

Ther must be something you are doing. The code I posted should save the active workbook or the workbook you are calling the SaveWorkbook SUB from.
Is the personal .xlsb visible and\or active when you call the code ?

I change to active workbook and it works great. Thank you

 

[Jaafar Tribak]

Ther must be something you are doing. The code I posted should save the active workbook or the workbook you are calling the SaveWorkbook SUB from.
Is the personal .xlsb visible and\or active when you call the code ?

I change to active workbook and it works great. Thank you

Careful when using ActiveWorkbook, as the current active workbook may not be the one you intend to save.

I would rather use the SaveWorkbook SUB I showed you in post#2 and pass it the correct workbook.