Error when saving macro workbook as non-macro VBA

[TheNeedsOfMany]

I'm trying to create a button on a sheet that allows for a non-macro enabled copy of a sheet to be saved via VBA. I feel like I am 90% of the way there, and have it set up so that the copy is being displayed correctly but it's throwing up an error when I try to save it (save dialogue box to select a path shows up and everything, but then an error dialog stating "Excel file could not be saved" displays.

I'm relatively new to macros and VBA so I'm sure I'm missing something simple, but any help is appreciated.

Code:

Sub SaveXLSXCopy()

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strTime As String
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant
Dim strVer As String
On Error GoTo errHandler

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet
strTime = Format(Now(), "yyyymmdd")
strVer = ThisWorkbook.Sheets("FFA Policy").Range("E46").Value

'set datestamp and version number for printing
[B6] = "Price list saved on " & strTime & " | ver. " & strVer

'hide "View FFA Policy" link for printing
Range("7:7").EntireRow.Hidden = True

'hide Save As icons
ActiveSheet.Shapes("SaveAsExcel").Visible = False
ActiveSheet.Shapes("SaveAsPDF").Visible = False

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'replace spaces and periods in sheet name
strName = Replace(wsA.Name, " ", "")
strName = Replace(strName, ".", "_")

'create default name for savng file
strFile = "NDL Price List - " & ActiveSheet.Range("C3") & " " & strTime & ".xlsx"
strFile = Replace(strFile, "Plumbing | ", "")
strFile = Replace(strFile, "HVAC-R | ", "")
strFile = Replace(strFile, "Universal | ", "")
strFile = Replace(strFile, " | ", "_")
strPathFile = strPath & strFile

'select folder for file
myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strPathFile, _
        FileFilter:="Excel Files (*.xlsx), *.xlsx", _
        Title:="Select Folder and FileName to save")

'save as xlsx if a folder was selected
If myFile <> "False" Then
    Application.DisplayAlerts = False

'copy-paste values and formatting to new workbook
Dim wsCopy As Worksheet, wsPaste As Worksheet
Dim wb As Workbook
Dim sFileName As String, sPath As String

Set wsCopy = ThisWorkbook.Worksheets("Price List")
Set wb = Workbooks.Add
Set wsPaste = wb.Sheets(1)

    wsCopy.UsedRange.Copy
    wsPaste.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    wsPaste.Range("A1").PasteSpecial xlPasteFormats
    wsPaste.Range("A1").PasteSpecial xlPasteColumnWidths
    ActiveSheet.Name = "NDL Pricing Snapshot"
    Rows(7).EntireRow.Delete

'save the file
wsPaste.Name = strFile
wb.SaveAs Filename:=strFile, FileFormat:=15

'confirmation message with file info
    MsgBox "Price list has been saved: " _
      & vbCrLf _
      & myFile
Application.DisplayAlerts = True

End If

exitHandler:
'Return to original workbook before exithandlder
wsCopy.Activate
Application.CutCopyMode = False
'unhide "View FFA Policy" link after printing
ActiveSheet.Range("7:7").EntireRow.Hidden = False

'remove printing datestamp
ActiveSheet.Range("B6") = ""

'unhide Save As icons
ActiveSheet.Shapes("SaveAsExcel").Visible = True
ActiveSheet.Shapes("SaveAsPDF").Visible = True

    Exit Sub
errHandler:
    MsgBox "Could not create Excel file"
    Resume exitHandler

End Sub

[#2]

@TheNeedsOfMany 

strFile is a filename without path, ending in .xlsx

But the line

wb.SaveAs Filename:=strFile, FileFormat:=15

has file format 15; this stands for .wk3, a Lotus 1-2-3 file format! I think you want

wb.SaveAs Filename:=myFile, FileFormat:=xlOpenXMLWorkbook 

[#3]

Good catch! Made the changes but am still getting the error. :(

[#4]

@TheNeedsOfMany 

Temporarily change the line

On Error GoTo errHandler

to

On Error GoTo 0

When the error occurs, click Debug in the error message box.

Which line is highlighted?

[#5]

wsPaste.Name = strFile

Do I have it in the wrong order? I'm trying to set a default filename for the user who wants to save a copy (which seems to be working as intended in the Save As dialogue box).

[#6 · Accepted solution]

@TheNeedsOfMany 

It might be that strFile is too long (the maximum length of a sheet name is 31), or that it contains characters that aren't allowed in a sheet name: \ , / , * , ? , : , [ , ].

When the code is paused, press Ctrl+G to activate the Immediate window, type

? strFile

and press Enter.

[#7]

Ah, this is almost certainly the case. I'm going to mess around with my filename string and test again. I'll be sure to reply with my findings!

[#8]

@Hans Vogelaar Worked! It's 31 including the .xlsx, I think. Thanks for your help, Hans!