Help with saving to a dynamic file name

[Basil_58]

Hello,
Newbie in VBA here.
The scenario is that I have files in .csv format, I run them though a Query and get the expected results.
So far so good.
I then want to copy the sheets from the current workbook to a new workbook with the same name but the .xlsx extension.
I am able to extract the name of the file though the FileDialog and save the file with one page to the new file with the correct .xlsx extension.
So far so good.
What I don't know how to do is to either
a) continue with the code that created and copied the first file or
b) append the sheets with some different code that will dynamically pickup the name of the new file.
The pages that I am trying to copy have code in them, so the option below where the code
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
would not work for the rest of the sheets.
Here is the code:
==============================================================
Sub GetFile()

Dim fileExplorer As FileDialog
Dim SelectedFile As Integer
Dim SelectedFilePath As String

Dim NewWb As Workbook


ThisWorkbook.Queries.FastCombine = True

Set fileExplorer = Application.FileDialog(msoFileDialogFilePicker)


'pick file
With fileExplorer
.Title = "Select the Captions file to Process"
.Filters.Clear
.AllowMultiSelect = False 'To allow or disable to multi select
.InitialFileName = ThisWorkbook.Path
.ButtonName = "Choose This File"

If .Show = -1 Then 'A file is selected
SelectedFilePath = .SelectedItems.Item(1)

Else ' else dialog is cancelled
MsgBox "You did not select a file"
SelectedFilePath = "" ' when cancelled set empty string as file path.
End If
End With

ThisWorkbook.Sheets("Start").Range("CaptionsFile").Value = SelectedFilePath

If SelectedFilePath <> "" Then

'run queries
ActiveWorkbook.RefreshAll

Set NewWb = Workbooks.Add

ThisWorkbook.Sheets("Client").Range("Client[#All]").Copy

With NewWb
With .Sheets(1)
With .Range("A1")
.Value = "Revised Captions"
.Font.Color = vbBlue
End With

With .Range("A3")
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With

.Columns("A:ac").AutoFit
.Columns("D").ColumnWidth = 10
.Rows.EntireRow.AutoFit
.Range("D4").Select
Sheets("Sheet1").Select
'***************Basil start**********************

Sheets("Sheet1").Name = "Client"
Application.CutCopyMode = False
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$3:$AC$5"), , xlYes).Name = _
"Table1"
Range("Table1[#All]").Select
ActiveSheet.ListObjects("Table1").Name = "ClientPreferences"
.Range("D4").Select

End With



.SaveAs Filename:=Replace(SelectedFilePath, ".csv", ".xlsx"), FileFormat:=xlOpenXMLWorkbook
End With

End If

ThisWorkbook.Save

End Sub
==============================================================
Your help will be greatly appreciated.
Basil

 

[Basil_58]

Hello everyone who looked at this question.
I resolved the issue.
I took out the code creating a new workbook and saved the same workbook with the different name and extension. So far so good.