Saving two files with changing names to a different location and then delete from Downloads folder

[jconkl02]

I'm trying to attack a problem from a different angle. I posted last week about wanting to open two .xls files in my Downloads folder that begin with "report" but have a ever changing numerical sequence after. For example, report1564504574214 or report1564504538987. Then I depending on what was in cell A1 of each WB, all of the contents of Sheet(1) would be copied and pasted into one of two worksheets in the WB that was executing the macro called "Daily Ticket Report.xlsm".

If the .xls file has "Program" in A1 then all of the data will be copied and pasted into "LastComment" WS in the Daily Ticket Report.xlsm WB.
If the .xls file has "Number" in A1 then all of the data will be copied and pasted into "Tickets" WS in the Daily Ticket Report.xlsm WB.

The problem is that I want to delete the report*.xls file after it's contents have been copied. The user will start with only two report*.xls files in their Downloads folder and I want them to be deleted so next time they pull the reports they again will start with just two report.xls filed.

JLGWhiz wrote a brilliant bid of code that should work, but when it goes to delete the open report*.xls file Excel spits out a runtime error:
Run-time error '-214722108 (800401a8)':Automation error

Sub t()Dim wb As Workbook, fPath As String, fName As String, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("LastComment")
Set sh2 = Sheets("Tickets")
fPath = "C:\Users\me\Downloads\"
fName = Dir(fPath & "Report*.xls")
    Do While fName <> ""
        Set wb = Workbooks.Open(fPath & fName)
        If wb.Sheets(1).Range("A1") = "Program" Then
            If sh1.Range("A1") = "" Then
                wb.Sheets(1).UsedRange.Copy sh1.Range("A1")
            Else
                wb.Sheets(1).UsedRange.Copy sh1.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        ElseIf wb.Sheets(1).Range("A1") = "Number" Then
            If sh2.Range("A1") = "" Then
                wb.Sheets(1).UsedRange.Copy sh2.Range("A1")
            Else
                wb.Sheets(1).UsedRange.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
            End If
        End If
        wb.Close False
        Kill wb.FullName
        fName = Dir
        Set wb = Nothing
    Loop

End Sub

So now I'm wondering if it would be easier to have Excel loop through the Downloads folder and just SaveAs the report*.xls files to something very generic like Data1.xlsx and Data2.xlsx in "C:\Users\me\Documents\Ticket Report". Then I would just be opening files that I do know the name of already, and I could just look at A1 of each file and determine what I wanted to do with the contents. Then when all is said and done, go back to Downloads and delete ALL .xls files that begin with the word "report".

Any ideas on how to accomplish either version of my diabolical plan?

 

[John_w]

That error occurs because every property of the 'wb' workbook, including FullName, is undefined after the wb.Close line. Here is the solution:

    Dim wbFile As String
    wbFile = wb.FullName
    wb.Close False
    Kill wbFile

 

[jconkl02]

Where does your solution go? Directly after the original wb.Close line?

That error occurs because every property of the 'wb' workbook, including FullName, is undefined after the wb.Close line. Here is the solution:

    Dim wbFile As String
    wbFile = wb.FullName
    wb.Close False
    Kill wbFile

 

[John_w]

My code replaces your Close and Kill lines. It stores the full name of the 'wb' workbook before closing it, so that the Kill knows which file to delete.

 

[jconkl02]

Works perfect. Thanks