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:

Sub Test()

Dim sh As Worksheet, flg As Boolean
For Each sh In Worksheets
If sh.Name Like "nr*" Then flg = True: Exit For
Next
If flg = True Then
MsgBox sh.Name
sh.Range(sh.Range("A2"), sh.Range("A250").End(xlUp)).Copy
Sheets("sumar").Cells(2, xllastcolumn + 1).End(xlUp)(2).PasteSpecial Paste:=xlValues
End If
End Sub

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.

Sub Test()

  Dim sh As Worksheet, flg As Boolean
  Dim Rng As Range
  Dim sh As Worksheet
  Dim xllastcolumn As Long
  
    For Each sh In Worksheets
      If sh.Name Like "nr*" Then
       MsgBox sh.Name
       Set Rng = sh.Range(sh.Range("A2"), sh.Range("A250").End(xlUp))
       xllastcolumn = xllastcolumn + 1
       Sheets("sumar").Cells(2, xllastcolumn).End(xlUp).Value = Rng.Value
      End If
    Next sh
    
End Sub

Hi danj and welcome to the forum.

Try this code which is in the attached.

Option Explicit
Sub MoveToSumar()
Dim ShtCtr As Double
Dim LastRow As Double
Dim SheetNo As Double
    For ShtCtr = 1 To Worksheets.Count
        If Left(Worksheets(ShtCtr).Name, 2) = "nr" Then
            SheetNo = Mid(Worksheets(ShtCtr).Name, 3)
            LastRow = Worksheets(ShtCtr).Cells(Rows.Count, "A").End(xlUp).Row
            Worksheets(ShtCtr).Range("A1:A" & LastRow).Copy _
            Destination:=Worksheets("sumar").Cells(1, SheetNo)
        End If
    Next ShtCtr
End Sub

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