Export XLS file using cell reference

Question

I have the following code designed to copy a worksheet to a new location.

Sub XLSSave()

    Sheets("Group Import").Copy
    Cells.Copy
    Cells.PasteSpecial xlPasteValues
    ActiveWorkbook.SaveAs Filename:=Sheets("Group Import").Range("B22")
    ActiveWorkbook.Close False
 
End Sub

Cell K67 is a file Path along the lines of
"C\Folder1\Folder2\Folder3\YYYY\MM\DD"
"C:\Folder1\Folder2\Folder3\YYYY\MM\DD".
The path "C:" was set correctly, I made a typo on the question.

I had intended to just concatenate the address within cell B22 as it needs to be dynamic.

It is exporting the Excel file as gibberish.

Answer 1

Export Worksheet

Easy

Option Explicit

Sub XLSSaveEasy()

    Application.ScreenUpdating = False

    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets("Group Import")
    sws.Copy

    Dim dws As Worksheet: Set dws = ActiveWorkbook.Worksheets(1)
    dws.UsedRange.Value = dws.UsedRange.Value

    Application.DisplayAlerts = False
    dws.Parent.SaveAs sws.Range("B22").Value, xlOpenXMLWorkbook
    Application.DisplayAlerts = True

    dws.Parent.Close False

    Application.ScreenUpdating = True

End Sub

Not So Easy

Sub XLSSave()
    
    Const swsName As String = "Group Import"
    Const swsFilePathCell As String = "B22"
    
    Dim sws As Worksheet: Set sws = ThisWorkbook.Worksheets(swsName)
    
    Dim FilePath As String: FilePath = sws.Range(swsFilePathCell).Value
    Dim FolderPath As String
    FolderPath = Left(FilePath, InStrRev(FilePath, "\") - 1)
    
    If Dir(FolderPath, vbDirectory) <> "" Then
        
        Application.ScreenUpdating = False
        
        sws.Copy
        
        Dim dws As Worksheet: Set dws = ActiveWorkbook.Worksheets(1)
        dws.UsedRange.Value = dws.UsedRange.Value
        
        Application.DisplayAlerts = False
        dws.Parent.SaveAs FilePath, xlOpenXMLWorkbook
        Application.DisplayAlerts = True
        
        dws.Parent.Close False
        
        Application.ScreenUpdating = True

        MsgBox "Backup of worksheet '" & swsName & "' created as '" _
            & FilePath & "'.", vbInformation, "Success"

    Else
        
        MsgBox "The Folder '" & FolderPath & "' does not exist.", _
            vbCritical, "Fail"
    
    End If

End Sub