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

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" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>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
>

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