Copy 1 column from multiple workbooks and paste into one worksheet

[Noidea1970]

I have little knowledge on VBA for Excel.
I generate multiple worksheets and have figured out how to put them into 1 workbook.
I need to get the data from the worksheets, from E5 and put them into the workbook starting with B5, than C5, etc.
I usually create 25, 37, or 57 work sheets. The length of column E5 will very, if possible it would be nice if it didn't pull anymore data than was created.
My Workbooks are labeled, CMM Data workbook 25 Template. The 25 will change to 37 or 57 depending on how many worksheets are generated.
When opened the tabs are labeled Measurements, Sheet1, Sheet1 (2), Sheet1 (3), etc Until either 25, 37 or 57 worksheets are generated. I would like column E5 from Sheet1, Sheet1 (2), Sheet1 (3) etc. copied and put into the Measurements workbook, starting at B5.

 

[JLGWhiz]

The source targets are vague, but we'll give it a shot. Copy the code to your code module1.

Sub t()
Dim sh As Worksheet, ssh As Worksheet, wb As Workbook, fPath As String, fName As String
Set sh = ThisWorkbook.Sheets(1) 'Edit sheet name
fPath = ThisWorkbook.Path & "\"
fName = Dir(fPath & "CMM Data *.xls*")
    Do While fName <> ""
        If fName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(fPath & fName)
                For Each ssh In wb.Sheets
                    If sh.Range("B5") = "" Then
                        ssh.Range("E5", ssh.Cells(Rows.Count, 5).End(xlUp)).Copy sh.Range("B5")
                    Else
                        ssh.Range("E5", ssh.Cells(Rows.Count, 5).End(xlUp)).Copy _
                        sh.Cells(5, Columns.Count).End(xlToLeft).Offset(, 1)
                    End If
                Next
            wb.Close False
        End If
        fName = Dir
    Loop
End Sub

 

[Noidea1970]

Sorry, I couldn't get it work. It would only populate the Excel form with #REF!.
What i'm doing is taking data that is generated by a machine inspecting parts for me. Every time I run a different part it creates an excel file in a folder for that part and PO number.
I found code that will allow me to open a window to search for where I keep the Excel files I need and then choose them and it will add them all to my, CMM Data workbook 37 Template. Here is the code I Found to add the Workbooks in to one Master workbook.
I have attached the Workbook i'm using with sheets I added to it, and the code I Found to add the Workbooks in to one Master workbook. Not sure if it will help you.
Thanks for your Help.

 

[Noidea1970]

I just retried your program at work and got it to work. Unfortunately it’s not the way I had hoped. I would like it pull data from the sheets that I have already added to the workbook.

 

[JLGWhiz]

I am going to pass on this one. I do not understand what you are trying to do.
Regards, JLG

 

[Noidea1970]

What I want is to Copy Column E5 (to when ever there is no data) say to E50, from 37 sheets in my workbook and put them into a Tab called Measurements., in that workbook. Starting at B5, than C5, D5,E5 etc. until all 37 sheets have had column E5 copied, pasted into the measurements tab.

 

[JLGWhiz]

Is this what you want?

Sub t()
Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Measurements" Then
            If Sheets("Measurements").Range("B5") = "" Then
                sh.Range("E2", sh.Cells(Rows.Count, 5).End(xlUp)).Copy Sheets("Measurements").Range("B5")
            Else
                sh.Range("B2", sh.Cells(Rows.Count, 5).End(xlUp)).Copy _
                Sheets("Measurements").Cells(5, Columns.Count).End(xlToLeft).Offset(, 1)
            End If
         End If
    Next
End Sub

 

[Noidea1970]

Thanks

 

[JLGWhiz]

Thanks

You're welcome,
Regards, JLG