Hi ,

Sorry for my English , hope you'll understand

I need to copy values of (50+) * 3 Cells
from about 60 workbooks ( i called them source workbooks ) to 1 Workbook
(destination workbook) .

Tried the following :

In every source workbook :

Set tempRange = Sheet1.Range(Cells(2, 1), Cells(RCount + 1, 9))
tempRange.Copy


and in destination workbook :

Sheet1.Cells(1, 1).PasteSpecial Paste:=xlValues,Operation:=xlNone
,SkipBlanks : = False, Transpose:=False

It works , but before i close one of the source workbook it asks
what to do with data in clipboard . It's pretty frustrating



So i wrote following function :

in source workbook :

Function getValues() As Variant
Dim TempArray As Variant
Dim Ilosc As Long
Dim i As Long
MaxRow = Sheet1.Cells(Cells.Rows.Count, 3).End(xlUp).Row
ReDim TempArray(1 To Ilosc, 1 To 3)
For i = 1 To MaxRow
TempArray(i, 1) = Arkusz1.Cells(i + 1, 2)
TempArray(i, 2) = Arkusz1.Cells(i + 1, 4)
TempArray(i, 3) = Arkusz1.Cells(i + 1, 9).Value2
Next i
getValues = TempArray
End Function


and in destination Workbook :

Dim MyVariable
Set MyRange = Sheet1.Range(Cells(RowStart, 1), Cells(RowEnd,3))
MyVariable= Application.Run("'" & NameofSourceWorkbook & "'" & "!" &
"getValues")
MyRange.Value=MyVariable


But it doesn't work i.e. there's no values in MyRange . Anyone knows
what's wrong ? Or have anyone idea how to solve this problem ?

TIA

Have you tried Application.DisplayAlerts = False

RBS

"mg" wrote in message
...
Hi ,

Sorry for my English , hope you'll understand

I need to copy values of (50+) * 3 Cells
from about 60 workbooks ( i called them source workbooks ) to 1 Workbook
(destination workbook) .

Tried the following :

In every source workbook :

Set tempRange = Sheet1.Range(Cells(2, 1), Cells(RCount + 1, 9))
tempRange.Copy


and in destination workbook :

Sheet1.Cells(1, 1).PasteSpecial Paste:=xlValues,Operation:=xlNone
,SkipBlanks : = False, Transpose:=False

It works , but before i close one of the source workbook it asks
what to do with data in clipboard . It's pretty frustrating



So i wrote following function :

in source workbook :

Function getValues() As Variant
Dim TempArray As Variant
Dim Ilosc As Long
Dim i As Long
MaxRow = Sheet1.Cells(Cells.Rows.Count, 3).End(xlUp).Row
ReDim TempArray(1 To Ilosc, 1 To 3)
For i = 1 To MaxRow
TempArray(i, 1) = Arkusz1.Cells(i + 1, 2)
TempArray(i, 2) = Arkusz1.Cells(i + 1, 4)
TempArray(i, 3) = Arkusz1.Cells(i + 1, 9).Value2
Next i
getValues = TempArray
End Function


and in destination Workbook :

Dim MyVariable
Set MyRange = Sheet1.Range(Cells(RowStart, 1), Cells(RowEnd,3))
MyVariable= Application.Run("'" & NameofSourceWorkbook & "'" & "!" &
"getValues")
MyRange.Value=MyVariable


But it doesn't work i.e. there's no values in MyRange . Anyone knows
what's wrong ? Or have anyone idea how to solve this problem ?

TIA