PDA

View Full Version : Solved: Copying Data from Multiple Excell Files - Range function problem Help



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

Simon Lloyd
07-04-2012, 12:20 AM
Just before this lineSet rDest = shDest.Cells(shDest.Rows.Count, 3).End(xlUp).Offset(1, 0)
addmsgbox "shDest now is " & shDest, vbOKOnly, "Sheet Title"and after the set line putmsgbox "rDest address is " & shDest.Cells(shDest.Rows.Count, 3).End(xlUp).Offset(1, 0).Address, vbOKOnly, "Destination Address"this will help you see whats going on at each run and will help you debug :)

afzalw
07-04-2012, 12:32 AM
Thanks, I removed copy paste functions, instead Now, I am using this code I found in another website.
Set rDest = shDest.Cells(shDest.Rows.Count, 3).End(xlUp).Offset(1, 0)
rDest.Resize(1, 2).Value = wbSource.Sheets("Distress Data").Range("K5:L5").Value