Help on a consolidation macro

[bobbak]

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

 

[Ron de Bruin]

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

 

[bobbak]

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

 

[Ron de Bruin]

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