I found code from Microsoft to Export Data to PDF or XPS Using the Excel.ExportAsFixedFormat Method and was able to make it work with a macro I created but it only works every other time I use it. Each time it doesn't work I get a VB Run-time error '1004': PasteSpecial method of range class failed. I hit the debug button and find Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Transpose:=False highlighted. If I reset the project it runs fine. I think it has to do with sheet protection but I haven't been successful finding anything online to fix it. My code is below.
Sub Create_MatchReport()
'
' Create_MatchReport Macro
'
'
Application.ScreenUpdating = False
Sheets("MatchCapture").Visible = True
Sheets("MatchCapture").Select
Range("B1:P30").Select
Selection.Copy
Sheets("MatchReport").Select
ActiveSheet.Unprotect
Range("B1:P30").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("B6:P29").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("MatchReport").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("MatchReport").Sort.SortFields.Add Key:=Range( _
"B6:B29"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("MatchReport").Sort
.SetRange Range("B6:P29")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("B1:P29").Select
ActiveSheet.PageSetup.PrintArea = "$B$1:$P$29"
Sheets("MatchCapture").Select
Range("A2").Select
Sheets("MatchCapture").Visible = False
Sheets("MatchReport").Select
' Export Data to PDF or XPS Using the Excel.ExportAsFixedFormat Method - Starts here
Dim rng As Range
Set rng = Range("$B$1:$P$29")
Dim fileName As String
' Change this file name to meet your own needs:
fileName = "C:\Users\Tony\Documents\Pool League\Export.pdf"
' Many of these properties are optional, and are included
' here only to demonstrate how you might use them. The
' Type parameter can be one of xlTypePDF and xlTypeXLS;
' the Quality parameter can be one of xlQualityStandard and
' xlQualityMinimum. Setting the OpenAfterPublish property
' to True will fail if you don't have a default viewer
' installed and configured.
rng.ExportAsFixedFormat Type:=xlTypePDF, _
fileName:=fileName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=True, _
From:=1, To:=1, OpenAfterPublish:=True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheets("MatchReport").Select
End Sub
I have a second item that i would like to accomplish. Is there a way to have the file name of the out put for fileName = "C:\Users\Tony\Documents\Pool League\Export.pdf" set the file name based on a cell value on the MatchReport sheet?
Thanks for you help.
Bookmarks