Excel: Automatically transfer data from multiple workbooks into a master workbook

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

This question generated 25 answers. To proceed to the answers, click here.

This thread is current as of May 28, 2014.

For more resources for Microsoft Excel