Help on a consolidation macro

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

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

Where do I enter the worksheet name that I want to copy?
thanks,

The example use the first sheet (sheet index)

Set sourceRange = mybook.Worksheets(1).Range("A1:B2")

See the Tips on the webpage how to change it
http://www.rondebruin.nl/copy3.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"bobbak" <[email protected]> wrote in message
news:[email protected]...
>
> Where do I enter the worksheet name that I want to copy?
> thanks,
>
>
> --
> bobbak
> ------------------------------------------------------------------------
> bobbak's Profile: http://www.excelforum.com/member.php...o&userid=22495
> View this thread: http://www.excelforum.com/showthread...hreadid=480195
>