Macro problem copying from closed files

[ArchiEMS]

Alright, I've hit the wall, I have been looking thru the board for about a week and found someone with a similar problem to mine. I want to copy a block of cells from different files, which are closed, to one file so I can do some analysis of this file.(I want to create a phantom master file of the others, so I can extract data and use it in analysises). That being said I have used a macro from here and modified it for my purposes. But, It will only copy the block of cells from the first sheet correctly and places "#Value" in the other cells from the other sheets. I have verified the "#Value" cells are coming from the other sheets but are not displaying the data. If anyone is up for the problem, let me know and I can post the code.

Thank you,
EMS

[kkknie]

Post away!

[ArchiEMS]

Sub expermient6()
Dim sdir As String
Dim Datarg As Range
Dim myCells As String
Dim Files
Dim x As Variant
'This is the directory being searched
sdir = "c:My DocumentsECMAC Analysis"
' Location of the cells on sheet 1
myCells = "Sheet1'!$A$7:$G$21"
Files = Dir(sdir & "*.xls")
'Speeding things up
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
x = 7
On Error GoTo FileError
Do While Len(Files) > 0
Range("A" & x, "G" & (x + 15)) = "='" & sdir & "[" & Files & "]" & myCells
Set Datarg = Range("A7", "G" & (x + 15))
x = x + 15
Files = Dir()
'Copying now
Application.Calculate
Datarg.Copy
Datarg.PasteSpecial
Loop
Application.CutCopyMode = False
Set Datarg = Nothing
Application.Calculation = xlCalculationAutomatic
Application.Calculate
Application.ScreenUpdating = True
Exit Sub

[kkknie]

I've got an hour long+ meeting in a few minutes, but here's what I've seen.

It appears that when the formula:

='D:[Book1.xls]Sheet1'!$A$7:$G$21

is in the same range on the new sheet as it is on Book1.Sheet1, all is OK. When the new sheet range changes, you get the errors.

I created two dummy workbooks to search through and filled Sheet1 on both with a bunch of data. If I change the formula for
Book2 to

='D:[Book1.xls]Sheet1'!$A$22:$G$37, it pulls in the data from those cells.

I'll be out of pocket for a while, but in any case, I am not sure what the heck is happening. I'll make another attempt when I get back in.

Good luck,

K

[ArchiEMS]

Thanks for the effort, I think I will be working on it for a while so if you have any thought later let me know. In the mean time I will exlpore you idea, I haven't noticed that to this point yet.

Thanks,
EMS

[kkknie]

Aha!!!

I've done it quite a bit differently, but it does seem to work. The only question is whether you want to keep the references or the values in the new workbook. My way keeps the references, but if you want actual values, it would be simple to select the new range and copy, paste special values.

Here it is:

Sub expermient()

Dim Files
Dim strCol As String
Dim intRow As Integer
Dim sdir As String
Dim x As Integer
Dim tempStr As String

Sheets(1).Select

'Directory to be searched
sdir = "d:"

'Offset
x = 6

Files = Dir(sdir & "*.xls")

Do While Len(Files) > 0

For i = 1 To 15
For j = 1 To 7

tempStr = Chr(64 + j) & i + x
Cells(i + x, j).Value = "='" & sdir & "[" & Files & "]Sheet1'!" & tempStr
Next
Next

x = x + 15
Files = Dir()

Loop

End Sub

Let me know if you need any clarification.

K

[ArchiEMS]

Alright it's getting close and the code is definately shorter than mine. It's returning numbers and getting them from the various files but it's still just keeping the order. I need to get A7:G21 of all sheets(eventually there will be 144 sheets) and put them in columns A7:G"whatever is needed". It's now taking A7:G21 from the first file then taking A21:G35 from the next and so on... But I am getting numbers from the other sheets now, but it's just zeros. Any Ideas?

[ArchiEMS]

WHOOO HOOO!!! I got it to work. Thank you so much for your help K. The x was on both sides of the equation basically. I just replaced 6 for x in the tempStr. By the way if you have time what does "Chr(64+j) & i+6" do anyway. Once again thanks for your help and this board is awesome!!!!!!!!

thanks,
ems