I found code from Microsoft to Export Data to PDF or XPS Using the Excel.ExportAsF​ixedFormat 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.ExportAsF​ixedFormat 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.