procedure to copy values from different worksheets to a new one, given a value

Hello there

I am trying to realize a procedure to obtain a quick search for some codes I have I a small warehouse.

Basically, I have six worksheets where I have, on two different columns each,

the code, the quantity, the shelf

where it is stored.
what I have been asked for is:
given the shelf, to have got back all the codes and quantity that there are in this shelf.

My Idea is:
in a sheet called <<initial>>, create a cell where I write the value of the shelf (this value in a text).
Then, with a VB procedure, start activating first worksheet, start reading the first column.
if the shelf value corresponds to the required shelf, then activate a sheets called "Results" and write there all the value corresponding to code and quantity.

Then, when ended the column, I would move to the second column.
Once ended the second column I would activate the second worksheet.

But I am having some problems since the beginning, since I have problems in reading the value of the shelf column.


question 1
I am attaching how I have started to wrote the procedure

Please Login or Register  to view this content.

Please can anybody tell me why I can read the value of the cell ?

question 2.
If anybody has a simpler or smartes way to solve this topic, it is welcome !

Thanks !

many thanks

I'll take a look and try to learn from you !
Concerning your question:
I have to scan all the worksheets.
In every worksheet I have two columns that I need to scan both.

That is: in every column of every worksheet I can have codes that are included in the required shelf

Many thanks again

Hello

I am here attaching you the file modified as it is.
I deem that, since we have the data in two columns, we need to repeat the procedure changing the column.
Moreover, this line

Please Login or Register  to view this content.

Need to be modified, since I can't copy the whole line, because I am copying also the data from the second column and in this case there can't be the required shelf.

Thanks !

Dear Mr. RudiS

I would like to thank you for your kind answer !

Many many thanks !