Automatically transfer data from multiple workbooks into a master workbook

[Porkchopping]

I am trying to automatically transfer data from nonadjacent cells (C1, B5,B10,B16,B22,B28) from multiple workbooks in a masterworkbook folder from A2:F2. I am a novice at VBA. I am not able to copy as Range("C1,B5,B10,B16,B22,B28") and the way it currently is coded only the last copied range (B28) is pasted to the master workbook. The data pastes to A2 in the master workbook instead of F2 where I want it. I need help copying the cells from the workbooks into row 2 in the master bookbook. Thanks in advance. Here is what I currently have:
Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("C:\ToolFolder\WorkObjectives\")
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If
Workbooks.Open (MyFile)
Range("C1").Copy
Range("B5").Copy
Range("B10").Copy
Range("B16").Copy
Range("B22").Copy
Range("B28").Copy
ActiveWorkbook.Close
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 1), Cells(erow, 6))
MyFile = Dir
Loop
End Sub

 

[JLGWhiz]

OK, we will just have to do it the hard way then.
QUOTE]
It's nearly perfect. All the correct data from all the files copied and pasted over to the master workbook. The only thing is that it copied straight across row 2 and doesn't drop to row 3, row 4, etc. for each copied file. So instead of going to A3 it continues to G2.
Thanks again,
Ryan

Should have caught that. Try this:

Sub LoopThroughDirectory()
Dim MyFile As String, rng As Range, wb As Workbook, i As Long, j As Long
MyFile = Dir("C:\ToolFolder\WorkObjectives\*.xl*")
    Do While Len(MyFile) > 0
        If MyFile = "zmaster.xlsm" Then
            Exit Sub
        End If
        Set wb = Workbooks.Open(MyFile)
            With wb.Sheets(1)
                rngArr = Array("C1", "B5", "B10", "B16", "B22", "B28")
                j = 0
                For i = LBound(rngArr) To UBound(rngArr)
                    j = j + 1
                    .Range(rngArr(i)).Copy Workbooks("zmaster.xlsm").Sheets(1).Cells(Rows.Count, j).End(xlUp)(2)
                Next
            End With  
        wb.Close
        MyFile = Dir
    Loop
End Sub

 

[Porkchopping]

Should have caught that. Try this:

That worked perfectly. Thank you.
For some reason it's not copying every file from the folder but that happened with a couple other codes I tried so I'm sure it's something I can work out by looking at the file properties themselves.
Thanks again.

Ryan

 

[JLGWhiz]

That worked perfectly. Thank you.
For some reason it's not copying every file from the folder but that happened with a couple other codes I tried so I'm sure it's something I can work out by looking at the file properties themselves.
Thanks again.

Ryan

Check the file extension of those that were not being copied. If they are not Excel files, it will not copy because the "*.xl*" limits the files to only files with extensions beginning with xl. Another reason could be that the sheet is blank for those cells. The only way to know for sure is to walk through the procedure using F8. That can be tedious for that type of exercise.

Regards, JLG

 

[Porkchopping]

Check the file extension of those that were not being copied. If they are not Excel files, it will not copy because the "*.xl*" limits the files to only files with extensions beginning with xl. Another reason could be that the sheet is blank for those cells. The only way to know for sure is to walk through the procedure using F8. That can be tedious for that type of exercise.

Regards, JLG

It seems there was an issue with the files. Some were saved as Excel Workbook and some were saved as Excel 97- 2003 Workbook. I standardized them all to Excel Workbook and all the files appear in the Master Workbook now. Next battle - sorting the files chronologically and graphing the data.
Thank you so much for your help.

Ryan

 

[JLGWhiz]

It seems there was an issue with the files. Some were saved as Excel Workbook and some were saved as Excel 97- 2003 Workbook. I standardized them all to Excel Workbook and all the files appear in the Master Workbook now. Next battle - sorting the files chronologically and graphing the data.
Thank you so much for your help.
Ryan

You are welcome,
Regards, JLG

 

[B00Mbasti]

Hi,
I'm working on a project where i must extract a lot of data out of excel files.
To do that quickly i will use VBA.
So i used your previously posted code and i have adopted it:

Sub LoopThroughDirectory()
Dim MyFile As String, Filepath As String, rng As Range, wb As Workbook, i As Long, j As Long
MyFile = Dir("C:\Users\Maarten\Desktop\HTRI-EXCEL\")
Filepath = "C:\Users\Maarten\Desktop\HTRI-EXCEL\"
Do While Len(MyFile) > 0
If MyFile = "HTRI-TOT.xlsm" Then
Exit Sub
End If
Set wb = Workbooks.Open(Filepath & MyFile)
With wb.Sheets(1)
rngArr = Array("J12", "J13", "G15", "J15", "J18", "P12", "P13", "N15", "P15", "P18", "G20", "G21", "G24", "P20", "P21", "P22", "P23", "P24")
j = 0
For i = LBound(rngArr) To UBound(rngArr)
j = j + 1
.Range(rngArr(i)).Copy Workbooks("HTRI-TOT.xlsm").Sheets(1).Cells(Rows.Count, j).End(xlUp)(2)
Next
End With
wb.Close
MyFile = Dir
Loop
End Sub

This works perfect (which i'm grateful for) but this only extract data of one sheet (sheet 1).
My question now is "how to extract data out of more then one sheet (ex. sheet 4) in the same way like this and in one code".


Sorry for my bad english, i hope u understand my question.
My thanks

Maarten