Saving Spreadsheet as PDF and Xlsx

[dlo1503]

Hello

I have code I'm currently running and it performs the task exactly as I would like it to to save the file as a PDF. I also want it to create an Xlsx copy as well but I am struggling to get the coding correct.
Anything I've tried either overwrites the original file or it throws up run-time error"5" (invalid procedure call or argument) in the VBA.

I want to save the new files as the value of Q2 which is working well for the PDF

Thanks
Daniel

Sub Button6_Click()
Range("B8:J36").Sort Key1:=Range("C8"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Dim rngRange As Range
Set rngRange = Worksheets("Sheet1").Range("Q2")
Set rngRange2 = Worksheets("Sheet1").Range("Q2")
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
"FILE LOCATION" & rngRange & ".pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

ActiveSheet.ExportAsFixedFormat Type:=xlWorkbookNormal, filename:= _
"FILE LOCATION" & rngRange2 & ".xls" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
Range("c4") = Range("c4") + 1
Range("B9:J36").ClearContents
End Sub

 

[Trevor G]

Try using this

ActiveSheet.Copy
    ActiveWorkbook.SaveAs Filename:="FILE LOCATION" & rngRange2 & ".xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
Range("c4") = Range("c4") + 1
Range("B9:J36").ClearContents

 

[dlo1503]

Appreciate that, thanks

 

[Trevor G]

Happy to help, thanks for the reply.