Subscript out of range - Array empty

Hello all!

I've attached a sample of my workbook. What I'm trying to accomplish is that on Sheet2 there is a list that I need to find in Sheet1 and then copy all rows associated with this entry. In this case the code is supposed to find the first item (100000) and then copy the row it is in as well as the following 6 and paste to a new sheet. Then do it for the remaining items in the list. I am getting a "subscript out of range" error and I've found that it is because my array "arr2find" is not storing the list from Sheet2. What am I missing or not understanding?!

Any help would be appreciated, thank you

Example.xlsm

There are two errors on the code.
I have corrected the first one, but I am not sure if the second correction is right for you (Highlighted in Red)

Please Login or Register  to view this content.

Thanks AB33...it got me past that error, but the offset line is only grabbing the row the number is found on and not the row + the 6 below it. I'll need to work out the pasting part...but is there a way to copy the 7 rows all together?

okay, I updated the

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Which allows for the first entry to copy the 7 rows, but the 2nd only takes the row with the next list entry....

Please Login or Register  to view this content.

RESIZE! Brilliant. I wish I worked with vba more to remember everything. SO MANY THANKS