Try the code below.
Note you'll need to update the code to reflect your workbooks' names and location (I've commented out yours so you'll just need to uncomment them and comment-out mine)
Read the comments in the code, especially the one "the above line tries to open this workbook".
The macro opens the xlsx file, but only long enough to make a copy of the Spending Detail Report sheet to a new workbook.
All subsequent operations are on this copy which you can do what you want with because it won't be saved later.
The totals row is deleted before the table is made into a proper Excel table, after which the table has its own total row added, which isn't hidden by the filtering.
Then the filtering and pdf creation loop is run, after which the temporary new workbook is closed (without saving).
Sub SDtoPDF()
Dim DirectoryLocation
Dim wbk_worklist As Workbook
'same as workbook this macro lives in
Dim ws_unique As Worksheet
Dim wbk_SD As Workbook
Dim ws_SD As Worksheet
'Dim iLastRow As Long
Dim iLastRow_unique As Long
Dim UniqueRng As Range
Dim Cell As Range
'Dim LastRow As Long
'Dim LastColumn As Long
Dim sfldr As String
Dim ListObj As ListObject, Name, totalcell As Range
Application.ScreenUpdating = False
'Note that the macro will save the pdf files in this active directory so you should save in an appropriate folder
DirectoryLocation = ActiveWorkbook.Path
'sfldr = "C:\Users\Tanya.Mcilravy\OneDrive - USDA\Documents\1Automation\SDPD\Separate Reports\"
sfldr = "C:\Users\Public\Documents\"
'Set wbk_SD = Workbooks.Open(fDialog.SelectedItems(1))
'Set wbk_SD = Workbooks.Open(sfldr & "SAMPLE SD HANA_-_Spending_Detail_Report_NARST_-_FT_08_15_19.xlsx")
'Set wbk_worklist = Workbooks.Open(sfldr & "SAMPLE SDPD worklist VBA.xlsm")
Set wbk_SD = Workbooks.Open(sfldr & "vbaExpress69297SAMPLE SD HANA_-_Spending_Detail_Report_NARST_-_FT_08_15_19.xlsx")
Set wbk_worklist = Workbooks.Open(sfldr & "vbaExpress69297SAMPLE SDPD worklist VBA.xlsm")
'the above line tries to open this workbook (the one with the code in). It couldn't be running this macro without it being open so you can instead use:
'Set wbk_worklist = ThisWorkbook
'Set wbk_PD = Excel.Workbooks("ZANALYSIS_PATTERN PDF source")
'add later
Set ws_unique = wbk_worklist.Sheets("SD_List")
iLastRow_unique = ws_unique.Cells(Rows.Count, "A").End(xlUp).Row
Set UniqueRng = ws_unique.Range("A2:A" & iLastRow_unique)
wbk_SD.Sheets("Spending Detail Report").Copy 'copies the sheet to a new workbook.
Set ws_SD = ActiveSheet
wbk_SD.Close (False) 'here the file is closed, left untouched.
With ws_SD
'to find the total row to delete it, either:
Set totalcell = Intersect(.Range("B2").CurrentRegion, .Range("B:B")).Find("total", LookIn:=xlFormulas, lookat:=xlPart, MatchCase:=False, searchformat:=False)
If Not totalcell Is Nothing Then totalcell.EntireRow.Delete
'or:
'********************************
' With .Range("B2").CurrentRegion
' 'or .find in that column?
' If InStr(1, .Cells(.Rows.Count, 1).Value, "total", vbTextCompare) > 0 Then .Cells(.Rows.Count, 1).EntireRow.Delete
' End With
'*********************************
If .FilterMode Then .ShowAllData
Set ListObj = .ListObjects.Add(xlSrcRange, .Range("B2").CurrentRegion, , xlYes) 'this makes a proper Excel table.
ListObj.ShowTotals = True 'adds a total row
'page setup here should WORK too...
With .PageSetup
.PrintArea = ListObj.Range.Address
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
For Each Cell In UniqueRng
ListObj.Range.AutoFilter Field:=4, Criteria1:=Cell
Name = DirectoryLocation & "\" & Cell.Value & " _SD" & ".pdf"
ws_SD.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Name, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
Next Cell
End With
ws_SD.Parent.Close False 'close the temporary new workook withut saving.
Application.ScreenUpdating = True
End Sub