Porkchopping
New Member
- Joined
- May 28, 2014
- Messages
- 12
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
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