afzalw
07-03-2012, 10:10 PM
I want to copy some data from multiple excell files in my different folders, I am getting some problems in pasting the range function. I found this code on net and did some changes and now its having some problems executing the line Range("rDest").Select
I just want to paste all things starting from in rDest range.
Thanks
:help
Sub CopySourceValuesToDestinationEdited2()
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sDestPath As String
Dim sSourcePath As String
Dim shDest As Worksheet
Dim rDest As Range
Dim vaFolder As Variant
Dim vaFiles As Variant
Dim i As Long
'array of folder names under sDestPath
vaFolder = Array("ABC", "DEF", "GHI", "JKL")
'array of file names under the respective folders in vaFolder
vaFiles = Array("ABCFile.xls", "DEFFile.xls", "GHIFile.xls", "JKLFile.xls")
sDestPath = "D:\A\B\C\"
sSourcePath = "D:\A\B\C\"
'Open the destination workbook at put the destination sheet in a variable
Set wbDest = Workbooks.Open(sDestPath & "a.xlsx")
Set shDest = wbDest.Sheets(1)
'loop through the folders
For i = LBound(vaFolder) To UBound(vaFolder)
'open the source
Set wbSource = Workbooks.Open(sSourcePath & vaFolder(i) & "\" & vaFiles(i))
'find the next cell in col C
Set rDest = shDest.Cells(shDest.Rows.Count, 3).End(xlUp).Offset(1, 0)
'write the values from source into destination
wbSource.Sheets("Distress Data").Range("B5,K5:L5,B16,K16:L16,B28,K28:L28,B40,K40:L40,B52,K52:L52,B64,K64:L64,B76,K7 6:L76,B88,K88:L88,B100,K100:L100,B112,K112:L112,B124,K124:L124,B136,K136:L1 36,B148,K148:L148,B160,K160:L160,B172,K172:L172,B184,K184:L184,B196,K196:L1 96").Select
Selection.Copy
Range("rDest").Select
ActiveCell.Paste
Next i
End Sub
I just want to paste all things starting from in rDest range.
Thanks
:help
Sub CopySourceValuesToDestinationEdited2()
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sDestPath As String
Dim sSourcePath As String
Dim shDest As Worksheet
Dim rDest As Range
Dim vaFolder As Variant
Dim vaFiles As Variant
Dim i As Long
'array of folder names under sDestPath
vaFolder = Array("ABC", "DEF", "GHI", "JKL")
'array of file names under the respective folders in vaFolder
vaFiles = Array("ABCFile.xls", "DEFFile.xls", "GHIFile.xls", "JKLFile.xls")
sDestPath = "D:\A\B\C\"
sSourcePath = "D:\A\B\C\"
'Open the destination workbook at put the destination sheet in a variable
Set wbDest = Workbooks.Open(sDestPath & "a.xlsx")
Set shDest = wbDest.Sheets(1)
'loop through the folders
For i = LBound(vaFolder) To UBound(vaFolder)
'open the source
Set wbSource = Workbooks.Open(sSourcePath & vaFolder(i) & "\" & vaFiles(i))
'find the next cell in col C
Set rDest = shDest.Cells(shDest.Rows.Count, 3).End(xlUp).Offset(1, 0)
'write the values from source into destination
wbSource.Sheets("Distress Data").Range("B5,K5:L5,B16,K16:L16,B28,K28:L28,B40,K40:L40,B52,K52:L52,B64,K64:L64,B76,K7 6:L76,B88,K88:L88,B100,K100:L100,B112,K112:L112,B124,K124:L124,B136,K136:L1 36,B148,K148:L148,B160,K160:L160,B172,K172:L172,B184,K184:L184,B196,K196:L1 96").Select
Selection.Copy
Range("rDest").Select
ActiveCell.Paste
Next i
End Sub