Saving Spreadsheet as PDF and Xlsx

dlo1503

New Member
Joined
Feb 24, 2020
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,692
Office Version
  1. 2016
Platform
  1. Windows
Try using this

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

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,692
Office Version
  1. 2016
Platform
  1. Windows
Happy to help, thanks for the reply.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,102
Messages
5,576,135
Members
412,699
Latest member
Dmetcalf2021
Top