Try it like this
Sub Example5()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceCcount As Long
Dim N As Long
Dim Cnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant
SaveDriveDir = CurDir
MyPath = "C:\Data"
ChDrive MyPath
ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
Cnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet
For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A1:B2")
SourceCcount = sourceRange.Columns.Count
Set destrange = basebook.Worksheets(1).Cells(1, Cnum)
sourceRange.Copy destrange
mybook.Close False
Cnum = Cnum + SourceCcount
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl
"bobbak" wrote in message
...
I am trying to collect data from a bunch of sheets. But when I run the
macro it pastes the information in the same column. I would like to
copy the information in rows next to each other.
Can anyone help?
Example. This is how it currently posts the data
12
34
56
78
90
This is how I wish to post the data
12 34 56 78 90
Thanks, in advance,
K
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet
For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets("Detail Testing
Results").Range("c3:d30")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")
basebook.Worksheets(1).Cells(rnum, "AE").Value =
mybook.Name
' This will add the workbook name in column D if you want
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
Resize(.Rows.Count,
Columns.Count)
End With
destrange.Value = sourceRange.Value
mybook.Close False
rnum = rnum + SourceRcount
Next
End If
ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub
--
bobbak
------------------------------------------------------------------------
bobbak's Profile: http://www.excelforum.com/member.php...o&userid=22495
View this thread: http://www.excelforum.com/showthread...hreadid=480195