View Full Version : Solved: Copy range from multiple excel files in one file

06-04-2009, 03:03 PM
I want to copy a range from Sheet1!A6 to Sheet1!Z100 from multiple files into one excel workbook called Holiday. Ideally the code only needs to copy any row with numbers in it, as often only rows down to about 20 are have information in. There could be anywhere from 1 to 26 files that I want to copy this range from. The information can be just pasted on sheet one of the Holiday workbook and each file it copies in will just paste below the last on copied.
I hope that makes some sort of sense...

Now looking around, I found some code that almost works, but for some reason it does not always copy the same range into the Holiday file. It seems to do the first workbook fine, but then starts to copy row A4 to Z4 instead, which isn't in the initial range, and it seems to ignor rows with information in?
Sorry if this is long winded, and i'm more than happy to forget this code if there is a better way.
Thank you so much

Sub FindAndAppendFiles()
Dim fs As FileSearch
Dim NewWB As Workbook
Set fs = Application.FileSearch
With fs
.LookIn = ThisWorkbook.Path
.FileType = msoFileTypeExcelWorkbooks
End With
With fs
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
If Not .FoundFiles(i) Like "*Holiday.xls" Then
Set NewWB = Workbooks.Open(.FoundFiles(i), Password:="password")
NewWB.Sheets(1).Range(NewWB.Sheets(1).Range("A6"), NewWB.Sheets(1).Range("z100").End(xlUp)).Copy
ThisWorkbook.Sheets(1).Range("A60000").End(xlUp).Offset(1).PasteSpecial xlValues
NewWB.Close False
End If

End If
End With

End Sub

06-04-2009, 03:13 PM
Have a look at this (http://vbaexpress.com/forum/showpost.php?p=186911&postcount=3)

06-05-2009, 01:25 AM
I'm not entirely sure what you mean about the size of the task.
Each excel file has a list of names / numbers down column a & b starting at row 6 and then some further numbers in corresponding cols along to column z.
The number of rows of names / Numbers will go no further down than row 100 but it may be many fewer than that.
The total number of files will never be more than 26 but will be less than that most of the time.
I want to copy that range A6:Z100 into one new file (called holiday) from each of the posible 26 files. It will all paste on to one sheet, and follow on from the end of the last files information that was pasted in.
eg if a file has names and info from A6:Z6 to A26:Z26 it will paste that into Sheet 1 of Holiday from A1:Z20. The next file's info will be pasted in to the Holiday Sheet 1 from A21:Z? depending how long it was.
Sorry if this is just the ramblings of an idiot...
Thank you

06-05-2009, 01:30 AM
It was the first line that was the important one. The rest is just "Signature". The code I posted is, I believe, adaptable to your use.

06-05-2009, 03:26 AM
Thank you for your reply, I really appeciate your help. I didn't think I could make it work, but now I think i'm getting somewhere. Thanks again for your time


06-05-2009, 05:33 AM
Can you post a sample workbook?