VBA to create new worksheet and using values from original workbook for SaveCopyAs name and path

[GuyGadois]

I have the following VBA which works to take data from the Workbook, create a new workbook and paste that data. Now I want code to utilize the SaveCopyAs to automatically save the file with name (fname) and path (fpath) taken from the original Workbook. I just don't know how to do this. The declared values don't move to the newly created workbook. Any help is really appreciated.

Guy

Sub Export_G_MS()Dim R1 As Range, R2 As Range, R3 As Range, nxRw As Long
Set R1 = Range("Export_G_Portfolios")


    Dim Fname           As String
    Dim fPath           As String


    Fname = Range("Export_Name").Value 'getting export name from Workbook
    fPath = Range("FilePath").Value 'getting file path from Workbook


Dim sFileSaveName As Variant
    


On Error Resume Next
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
End With


  R1.SpecialCells(xlCellTypeVisible).Copy
  Set NewBook = Workbooks.Add
   Dim wb As Workbook


ActiveSheet.Name = "ExportMS"


  NewBook.Worksheets("ExportMS").Range("A1").PasteSpecial (xlPasteValues), Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False




On Error Resume Next


nxRw = Sheets("ExportMS").Cells(Rows.Count, "A").End(xlUp).Row + 1


For i = Sheets("ExportMS").Range("A1:A" & nxRw).Rows.Count To 1 Step -1
    If Sheets("ExportMS").Cells(i, "A").Value = "" Then Sheets("ExportMS").Cells(i, "A").EntireRow.Delete
Next i
    
Columns(2).NumberFormat = "##.#0%"
Columns(3).NumberFormat = "$##,#0"
Columns(4).NumberFormat = "##"


With Application
    .CutCopyMode = False
    .DisplayAlerts = True
    .ScreenUpdating = True
End With




'Save_As Now I want the new worksheet to automatically save in fPath using fname
         
     ActiveWorkbook.SaveCopyAs fPath & Fname & " - " & Format(Date, "YYYY.MM.DD") & ".xlsx"
     
     MsgBox "The File Has Been Saved as an Excel File" & vbNewLine & vbNewLine & "File Name: " & Fname & vbNewLine & "Destination: " & fPath & vbNewLine




End Sub

 

[GuyGadois]

Bump for AM group

 

[GuyGadois]

Anyone? Is my description confusing or vague?