Find all occurances of Value1, copy rows A-E into a new sheet

[fahnskap]

Hello,

This is my first post on these forums, but after having spent better part of my day trying to find a VBA solution to my problem online, I figured it would be worth a shot to post on here in case someone might have any idea how to approach this.

For the sake of being able to explain this without sounding too confusing, I'll call the value from the InputBox in Sheet1 "Value1".

I've got three sheets, in Sheet1 there is an InputBox where the user will input the value to search for in column A of Sheet2. The VBA code needs to then copy cells in rows A-E where it found Value1 in A to Sheet3, and when a different number is entered in the InputBox (Value1) that information needs to be pasted underneath the already existing information in Sheet3 that got put there from the previous time the VBA code was run.

Here's a short example of what the data sheet (Sheet2) where is searches for Value1looks like:
A B C D E
MANIFEST1 29092011 ORDER1 PART1 UNIQUEID1
MANIFEST1 29092011 ORDER1 PART2 UNIQUEID2
MANIFEST1 29092011 ORDER1 PART3 UNIQUEID3
MANIFEST2 29092011 ORDER2 PART4 UNIQUEID4
MANIFEST3 29092011 ORDER3 PART5 UNIQUEID5
MANIFEST3 29092011 ORDER3 PART6 UNIQUEID6

So if Value1 the first time this VBA code runs is entered as MANIFEST1, the VBA would need to copy cells A1-3 through E1-3 and paste it into Sheet3. Next time, they enter MANIFEST2, and A4 through E4 would need to be copied and pasted underneath the data that's already been copied into Sheet3 from the previous time the code was run.

Here is the code I found somewhere else online (I'm not exactly sure where anymore) and that I'm currently using. Even through I would maybe eventually manage to edit this code to also select cells A-E and copy them into Sheet3, I wouldn't know how to make it so it also copied A1-3 through E1-3 which also have the same value in A, or how to make it paste this new information underneath the data that's already there.

Please Login or Register  to view this content.

I realize this could prove to be a lot of work, but any help with this problem would be greatly appreciated.

Kind regards,
Chris

[Steffen Thomsen]

Hi,

try something like this

Please Login or Register  to view this content.

Steffen Thomsen

[tigeravatar]

Chris,

Attached is an example workbook based on the criteria you provided. Sheet1 has a button named Find Manifest that is assigned to the following macro:

Please Login or Register  to view this content.

[fahnskap]

Wow, really fast replies. Looks like they both do exactly what I want as well, that is just excellent.

Many thanks, Steffen and tigeravatar :-)

Much appreciated!