Copy range from multiple .xls files - output to separate columns per .xls file
 

I have a series of Excel files, and I have the code working to copy a
range from each and output into one sheet. When it is done with the
copy/paste from the first file, it continues to the next file but it
continues to paste in the cell directly below the last file. I need
it to start pasting the data in a new column per file. Here is what I
have so far:

Sub CopyRangeValues()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim i As Long
Dim a As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "filepath"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set basebook = ThisWorkbook
rnum = 1
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(1).Range("a5:c52")
a = sourceRange.Rows.Count
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum,
1). _

Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
mybook.Close
rnum = i * a + 1
Next i
End If
End With
Application.ScreenUpdating = True
End Sub

Let me know if any more info is needed.
Any help would be greatly appreciated.

-qwerty01

Copy range from multiple .xls files - output to separate columns per .xls file
 

See
http://www.rondebruin.nl/copy3.htm

Use
Merge a range from all workbooks in a folder (next to each other)



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"qwerty01" wrote in message oups.com...
I have a series of Excel files, and I have the code working to copy a
range from each and output into one sheet. When it is done with the
copy/paste from the first file, it continues to the next file but it
continues to paste in the cell directly below the last file. I need
it to start pasting the data in a new column per file. Here is what I
have so far:

Sub CopyRangeValues()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim i As Long
Dim a As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "filepath"
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
Set basebook = ThisWorkbook
rnum = 1
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
Set sourceRange = mybook.Worksheets(1).Range("a5:c52")
a = sourceRange.Rows.Count
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum,
1). _

Resize(.Rows.Count, .Columns.Count)
End With
destrange.Value = sourceRange.Value
mybook.Close
rnum = i * a + 1
Next i
End If
End With
Application.ScreenUpdating = True
End Sub

Let me know if any more info is needed.
Any help would be greatly appreciated.

-qwerty01

Copy range from multiple .xls files - output to separate columns per .xls file
 

That worked perfectly. Thank you Ron. Your site has been bookmarked,
and I will check there before posting any other questions from now on.

-qwerty01