save as vba

[michelleblanc]

Hi I am trying to write vba to save excel file with the prompt save as.
Included is the filename (based on cell value I6) that will appear in the save as prompt.
Problem is that i want the saveas prompt to save the excel file in the folder named in cell P6 where the original excelfile which include this vba script is located...

My scripts generates the save as prompt but not in the right folder...

Can somenone help me out please?

Thanks in advance!!

Here is my code:

Private Sub cbsaveas_Click()
Dim wb As Workbook
    Dim NewFileName As String
    Dim NewFileFilter As String
    Dim myTitle As String
    Dim FileSaveName As Variant
    Dim NewFileFormat As Long
   
    Set wb = ThisWorkbook
    If Application.Version >= 12 Then   'Version 12 is xl2007
      'Note: If file extension not included in B18 then concatenate it
      NewFileName = wb.Sheets("Hoofdblad prestudie").Range("I6").Value & ".xlsm"
      NewFileFilter = "Excel Macro-Enabled workbook (*.xlsm), *.xlsm"
      NewFileFormat = 52
    Else
      
      NewFileName = wb.Sheets("Hoofdblad prestudie").Range("I6").Value & ".xls"
      NewFileFilter = "Microsoft Excel Workbook (*.xls), *.xls"
      NewFileFormat = xlNormal
    End If
   
    myTitle = wb.Sheets("Hoofdblad prestudie").Range("P6").Value
   
    FileSaveName = Application.GetSaveAsFilename _
            (InitialFileName:=NewFileName, _
             FileFilter:=NewFileFilter, _
             Title:=myTitle)
    If Not FileSaveName = False Then
      wb.SaveAs FileName:=FileSaveName, _
                    FileFormat:=NewFileFormat
    Else
      Msgbox "Calculatie is niet bewaard. Gebruiker heeft het bewaren gecanceled."
    End If

End Sub