I use this macro every month without issue, now I am receiving an error. Invalid procedure call or argument. And this part is highlighted.
For Each fNAME In fileLIST
MyArr = Split(fNAME.Value, ", ")
For Each ws In ActiveWorkbook.Worksheets
If InStr(ws.Range("A1"), MyArr(2)) > 0 Then
If InStr(ws.Range("B1"), MyArr(0)) > 0 Then
If InStr(ws.Range("B1"), MyArr(1)) > 0 Then
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=fPATH & fNAME.Value & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Option Explicit
Sub EDNotepadPDF()
Dim wsLIST As Worksheet, ws As Worksheet, fPATH As String, Errors As Boolean
Dim fileLIST As Range, fNAME As Range, MyArr As Variant
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.InitialFileName = ActiveWorkbook.Path
.Show
If .SelectedItems.Count > 0 Then
fPATH = .SelectedItems(1) & "\"
Else
MsgBox "No destination selected, aborting..."
Exit Sub
End If
End With
Set wsLIST = ActiveWorkbook.Sheets("File Names")
Set fileLIST = wsLIST.Range("A:A").SpecialCells(xlConstants)
fileLIST.Offset(, 1).ClearContents
For Each fNAME In fileLIST
MyArr = Split(fNAME.Value, ", ")
For Each ws In ActiveWorkbook.Worksheets
If InStr(ws.Range("A1"), MyArr(2)) > 0 Then
If InStr(ws.Range("B1"), MyArr(0)) > 0 Then
If InStr(ws.Range("B1"), MyArr(1)) > 0 Then
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=fPATH & fNAME.Value & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
fNAME.Offset(, 1).Value = "exported"
Exit For
End If
End If
End If
If ws.Index = ActiveWorkbook.Sheets.Count Then
fNAME.Offset(, 1).Value = "NOT FOUND"
Errors = True
End If
Next ws
Next fNAME
Bookmarks