User Form that transfers data from one page to another

Here is the next problem Im trying to tackle.

I have data on sheet1 similar to this

Please Login or Register  to view this content.

I want a user form where the user can enter a Location (145) into a text box,
and the data from A6:B8 will show up on Sheet 2

So I need a way for the code to search for a marker like Location(A1), and then compare the value of B1 to see if it matches, if not move to the next marker (A5) and repeat.

Also since the number of cells between markers may be different, it should only transfer cells between the markers to sheet 2

Originally Posted by Carim

Hi,

I have a couple of questions for you...
1. When you say "transfer", do you mean copy or move ...?

Copy

2. Is the user input coded to be stored in column A ...?
3. Do you test your "marker", or could it be that the input has no match in column B ...?

Im afraid I dont understand these last 2 questions, but perhaps this will answer them.

The input should only look in column A for the marker.

The marker will always be the word Location, and it will only be in column A.

Originally Posted by Carim

Ok then ... it is the word Location which triggers the copy process ...

1. Is it A6:B8 or A6:B9 which needs to be copied... or more generally, is it the 3 rows below "Location" ... ?
2. What is the destination of the copy process ...?

I was just thinking, it could just search for the Location number which is entered in the User form. Whats really important is that it only copy the cells after the location number and stop when it reaches the next word Location.
The destination of the copy process can just be Sheet2 A1:B whatever based on how many cells are being copied. I will fine tune the results once I figure out how this works.

The final product will allow the user to enter a location, have the data for that location copied to sheet2, then the user will print sheet2, and repeat the process with a new location. Overall there are 60 locations.

Originally Posted by Carim

You are right ...
Could you just precise the following ... are the blocks, in between the word "Location", always of the same size or not ...?

the cells in between the word location are always 3 columns, A, B, C, but the number of rows will vary. Values in the cells are either text or numbers.

here is a sample of Sheet 1

Please Login or Register  to view this content.

Another thing, if its any easier, I could swap the word location with the location number so A1 would be 6003 and B1 would be the word location. I guess that might make it easier.

I could even add a row of characters at the bottom of each set of data to indicate a stopping point like

6003 Location
part# 13
part# 3
part# 4
XXX XXX XXX