Merging multiple workbook values instead of formulas

[angminer]

I am trying to merge a summary worksheet from multiple workbooks together. The summary sheet is comprised of formulas reading from other datasheets.

I found this code and have it working to read and copy the correct parts of the sheet, but I can't figure out how to get it to read and copy the values instead of the formula.

Sub LoopThroughOLDDirectory()


Dim MyFile As String, rng As Range, wb As Workbook, i As Long, j As Long
MyFile = Dir("O:\LAP Project\l*")
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If
Set wb = Workbooks.Open(MyFile)
With wb.Sheets(4)
rngArr = Array("$C$2", "$D$2", "$E$2", "$F$2", "$C$3", "$D$3", "$E$3", "$F$3", "$C$4", "$D$4", "$E$4", "$F$4")
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

Help would be appreciated.

Thanks,
Angie

[YasserKhalil]

Try this untested code

Please Login or Register  to view this content.

[daffodil11]

You could try moving the paste to the next line so you can invoke the PasteValues method.

Please Login or Register  to view this content.

[JOHN H. DAVIS]

Please use code tags when posting to the Forum. Refer to Rule 3 in the link.

http://www.excelforum.com/forum-rule...rum-rules.html

Maybe change this line:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

[angminer]

Thank you for all of your help! I was hoping it was something simple.

Both methods work perfectly.