Copy from multiple files and paste to one file vba 2010

[redhawk87]

This might of been asked before so sorry if it has (I had a hard time finding it). I have 24 excel spreadsheets and I would like to copy the exact same cells out of each of them and place them into a 25th spreadsheet. This is my code snippet.

'did some other stuff including opening file25
y = 0
Do
Workbooks.Open Filename:="C:\folder" & y & "\file" & y & ".xlsx"
Range("O7:W11").Copy
Windows("file25.aggregate").Activate
Range("O" & (y * 7 + 15)).Select
ActiveSheet.Paste
Workbooks("file" & y & ".xlsx").Close
y = y + 1
Loop Until y = 24
ActiveWorkbook.SaveAs "C:\file25", FileFormat:=xlOpenXMLWorkbook
'do some other stuff

The problem I am having is that the same exact thing gets pasted every time and it is always whatever I pasted first. Also, I have reason to believe on loop 23, it does not paste where it is suppose to paste. It pastes at Range("O8") instead of Range("O176"). I have tried changing "Range("O7:W11").Copy" to "Range("O7:W11").Select" and then adding "ActiveSheet.Copy" but that does not work.

So the output I get to my file25 is something like this

O P Q R S T U V W
15 | 23 33 13 18 92 44 23 77 4
....|
22 | 23 33 13 18 92 44 23 77 4
....|
29 | 23 33 13 18 92 44 23 77 4
....
When it should be:

O P Q R S T U V W
15 | 23 33 13 18 92 44 23 77 4
....|
22 | 47 82 59 69 68 24 32 66 8
....|
29 | 22 33 44 55 66 77 88 5 0
....



EDIT:
I found out that the Copy and Paste is working correctly the only problem is it is copying over the formulas and not the result of the formulas. For instance, Cell O15 could have the value "=AVERAGE(A1:A120)". I do not want to copy "=AVERAGE(A1:A120)" I want to copy "28.3" which is the average of the cells in A1 through A120. So I guess my question is a different one than I posted

[Medpack]

Try;

Please Login or Register  to view this content.

[redhawk87]

I get a compile error: expected: end of statement and FileName:= is highlighted in the first statement in the for loop.

[redhawk87]

I ended up fixing the problem. Turns out it was copying the correct things over, it was just copying the formulas in the cells instead of the values in the cell. To fix it I did this:

Range("O" & (y * 7 + 15)).PasteSpecial xlPasteValues

instead of:

Range("O" & (y * 7 + 15)).Select
ActiveSheet.Paste

[Medpack]

No problem and sorry about the error (wrote it outside of excel);

Should be;

Please Login or Register  to view this content.

Since I set it = to a variable, where as it was simply opening without a reference before.