VBA "SAVE AS" EXTENSION ISSUE

[Mr_Phil]

I have a template that I do a fast paste from the main workbook. Then I want to save the template with a unique name. But, it tosses up an error.

Sub Export()

Workbooks.Open FileName:=ThisWorkbook.Path & "\RSP PO Form Template New.xlsx"
Workbooks("DISH ORDER WORKSHEET.xlsB").Worksheets("Subcontractor PO Form").Range("H17:H500").Copy
Workbooks("RSP PO Form Template New.xlsx").Worksheets("RSP PO Form").Range("H17").PasteSpecial Paste:=xlPasteValues

  'Saves copy of workbook to filename as value of Q1
  Windows("RSP PO Form Template New.xlsx").Activate
  Dim PathName, FileName As String
  FileName = Range("Q1").Value & ".xlsx"
  If Len(FileName) > 0 Then
    PathName = ThisWorkbook.Path
    If Dir(PathName, vbDirectory) = "" Then MkDir PathName
    ThisWorkbook.SaveAs PathName & "\" & FileName
  Else
    Range("q1").Select
     MsgBox "File name in P1 is not found", vbExclamation, "Not saved": Exit Sub
  End If
  Range("F10").Select

End Sub

I thought it was fairly straightforward but I get this error and am not certain what I did wrong. I got the code from various google searches and put it together.

 

[mumps]

Untested:

Sub Export()
    Application.ScreenUpdating = False
    Dim wb As Workbook
    Set wb = ThisWorkbook
    Workbooks.Open FileName:=wb.Path & "\RSP PO Form Template New.xlsx"
    With Workbooks("RSP PO Form Template New.xlsx").Sheets("RSP PO Form")
        If .Range("Q1").Value <> "" Then
            Workbooks("DISH ORDER WORKSHEET.xlsb").Sheets("Subcontractor PO Form").Range("H17:H500").Copy
            .Range("H17").PasteSpecial Paste:=xlPasteValues
            .Copy
            ActiveWorkbook.SaveAs FileName:=wb.Path & "\" & .Range("Q1").Value & ".xlsx", FileFormat:=51
        Else
            MsgBox "File name in P1 is not found", vbExclamation, "Not saved"
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[Mr_Phil]

Thank you. That did the trick and I will look closer to learn it as opposed to simply using it. Have a great day.

 

[mumps]

You are very welcome.