Runtime error PasteSpecial method of Range class failed

I don't know why it error's.

Can I ask if there is a reason to create a new instance of Excel? You could open your inventory workbook within the same instance.

I suspect the error is due to multiple instances of Excel and maybe (guessing) that some instances are not closed when no MyName match is found because the Application.Quit is within the If-block and would only execute for a match.

When it errors, do a Ctrl+Alt+Delete to open the Windows Task Manager. How many instances of Excel do you see in the Processes list?

Thanks for information, I did notice several instances of excel open and tried to correct with Application.Quit. Fairly new with VBA and still learning.

Originally Posted by russ50

Thanks for information, I did notice several instances of excel open and tried to correct with Application.Quit. Fairly new with VBA and still learning.

This should open your inventory workbook in the same Excel instance.

Sub Test()
    
    Dim MyName As String
    Dim xlBook As Workbook
    Dim oRange As Range
    Dim wsDest As Worksheet
    
    Set wsDest = ActiveSheet
    MyName = ????
    
    Application.ScreenUpdating = False
    
    Set xlBook = Workbooks.Open("c:\temp\inventory.xlsx")
    Set oRange = xlBook.Worksheets("Sheet1").Columns(1)
    
    Set aCell = oRange.Find(What:=MyName, LookIn:=xlValues, LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not aCell Is Nothing Then
        firstName = aCell.Address
        Do
            nextRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row + 1
            aCell.Resize(, 4).Copy
            wsDest.Cells(nextRow, 1).PasteSpecial Paste:=xlValues
            Set aCell = oRange.FindNext(aCell)
        Loop While Not aCell Is Nothing And aCell.Address <> firstName
    End If
    
    Application.CutCopyMode = False
    xlBook.Close SaveChanges:=False 'close inventory workbook
    Application.ScreenUpdating = True
    
End Sub

Thanks again but I'm questioning the use of an explicit variable with .PasteSpecial method. I'm not nitpicking just learning.

Originally Posted by russ50

Thanks again but I'm questioning the use of an explicit variable with .PasteSpecial method. I'm not nitpicking just learning.

I don't know what that means? Is that a question or comment?