VBA copy/paste error

[rharn]

Hello, I am trying to create a code that will copy and paste the results of an array from a search function onto a worksheet. However, I am getting an 'overflow' error when I try to use a copy/paste code that has worked for me in the past. Can someone please help me figure out why this paste function crashes when it gets to the last line of 'ActiveSheet.Paste'?

        For i1 = LBound(Results1) To UBound(Results1)
            Set p1results = Range(Results1(i1))
            Range("D" & p1results.Value & ":P" & p1results.Value).Copy
            Sheets("SearchResult").Select
            NextRow = Range("D65536").End(xlUp).Row + 1
            Cells(NextRow, 1).Select
            ActiveSheet.Paste
        Next i1

 

[VoG]

Make sure that you have

Dim NextRow As Long

 

[rharn]

Hi, VoG thanks for suggestion, but even afterI change it to 'Long' i am still receiving the error. Any other explanations? Thanks in advance!

 

[VoG]

Does this work?

       For i1 = LBound(Results1) To UBound(Results1)
            Set p1results = Range(Results1(i1))
            Range("D" & p1results.Value & ":P" & p1results.Value).Copy Destination:=Sheets("SearchResult").Range("D" & Rows.Count).End(xlUp).Offset(1, -3)
        Next i1

 

[rharn]

VoG I just realized why it won't work but do not have a simple solution perhaps you can provide. The array Results1() provides the cell addresses from a search function that I created. As a result, I want to display the results of each cell from the search array along with the subsequent data associated with the found cell location that ranges from columns D:P. Is there a way to just extract the number from the cell or anything of that sort? Thanks in advance!