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
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