Retrieve data from specific sheets

Hello!
This is my fist post here and I'm rather new to Excel, so I'll probably cause a lot of laughs. Here is the matter:

I have a file with 50+ sheets, of which 45 are named nr1, nr2, nr3... nr(still growing). I need to collect all the data in the first column of each sheet that starts with "nrX" and add it into the Xth column of a different sheet (1st column of 1st sheet to 1st column, 1st column of 2nd sheet into 2nd column etc.). The data in those cells is text. The problem is that my lame script only reads the first sheet. Here is what I have so far:

Please Login or Register  to view this content.

I added the msgbox only to see which sheet actually gets read, if you're wondering why it's there. I can't seem to make it work past the first sheet.

Hello danj,

Welcome to the Forum!

Try this version of your macro.

Please Login or Register  to view this content.

Hi danj and welcome to the forum.

Try this code which is in the attached.

Please Login or Register  to view this content.

Hello and many thanks on your quick reply!
Here is the quick result:
1. sh was double defined and had to remove one instance
2. it only brings me the first cell in each column, not the whole column

EDIT> Sorry, it was working, you are indeed masterful at this :-)

EDIT 2> Actually, it was Marvin's stuff that was working, but many thanks to both of you!

I'm not sure Leith knows what sheet number it is using.
That is if you have nr1, nr2 and no 3 or 4 but nr5 next he might not put the result on sumar on the correct number column.

Test my code to see if it is what you want.

You could also do this with a formula.

With nr1, nr2, nr3, etc in cells A1 to AZ1, whatever, in cell A2:

=IF(ISERROR(INDIRECT(A$1&"!A"&ROW($A2))),"",INDIRECT(A$1&"!A"&ROW($A2)))&""

Drag down and across.

Copy and paste special values to remove the formulae.

You could, of course, use VBA to populate the cells and then convert to values but I guess you have several VBA approaches already ;-)

Regards