Help would be appreciated to resolve intermittentt runtime error, code fails at PasteSpecial. I would like to understand the error and not just a fix.
Any help to the issue would be great.
Set xlApp = New Excel.Application 'opens new instance
Set xlBook = xlApp.Workbooks.Open("c:\temp\inventory")
Set xlSheet = xlBook.Worksheets("Sheet1")
Set oRange = xlSheet.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 = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1
aCell.Resize(, 4).Copy
ActiveSheet.Cells(nextRow, 1).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Set aCell = oRange.FindNext(aCell)
Loop While Not aCell Is Nothing And aCell.Address <> firstName
Application.Quit
End If
End Sub
Bookmarks