How to copy range to diffrent workbook ?
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 |
How to copy range to diffrent workbook ?
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 |
All times are GMT +1. The time now is 12:31 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com