Combining data from cells from several excel sheets to a new sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a bunch of excel files, say S101.xls, S102.xls, ... S120.xls. I would
like to combine data from the same cell in each sheet to one table in a new
sheet. How can I do that easily?

Example of the resulting table in a new sheet in a new excel file:
cell b9 of sheet S101.xls - cell f11 sheet S101.xls - cell b25 sheet
S101.xls - etc.
cell b9 of sheet S102.xls - cell f11 sheet S102.xls - cell b25 sheet
S102.xls - etc.
....
cell b9 of sheet S120.xls - cell f11 sheet S120.xls - cell b25 sheet
S120.xls - etc.

Thanks a lot,

Rik
 
This formula in any cell in a new book will return the value in cell A1 of
Sheet1 of book S101.xls..............

=[S101.xls]Sheet1!A1

Copy and paste and/or adjust the filename, sheet, or cell accordingly to fit
your need..........

Vaya con Dios,
Chuck, CABGx3
 
Hi CLR,

Thanks for your reply. I am aware of that solution, but (as in my case) with
a large table and a lot of files, changing each cell manually is not
feasible. Unfortunately, Excel does not automatically change the filename
when extending your choice to other cells, as cell references do.

Thanks,

Rik


CLR said:
This formula in any cell in a new book will return the value in cell A1 of
Sheet1 of book S101.xls..............

=[S101.xls]Sheet1!A1

Copy and paste and/or adjust the filename, sheet, or cell accordingly to fit
your need..........

Vaya con Dios,
Chuck, CABGx3


Rik said:
Hi,

I have a bunch of excel files, say S101.xls, S102.xls, ... S120.xls. I would
like to combine data from the same cell in each sheet to one table in a new
sheet. How can I do that easily?

Example of the resulting table in a new sheet in a new excel file:
cell b9 of sheet S101.xls - cell f11 sheet S101.xls - cell b25 sheet
S101.xls - etc.
cell b9 of sheet S102.xls - cell f11 sheet S102.xls - cell b25 sheet
S102.xls - etc.
...
cell b9 of sheet S120.xls - cell f11 sheet S120.xls - cell b25 sheet
S120.xls - etc.

Thanks a lot,

Rik
Click to expand...
Click to expand...
 
Hi Ron,

This was very useful. Thanks a lot. I adapted it a bit so it can do
translation from source file column to destination file row and it can do
more copies from the same source file. I enclosed my sloppy code (havily
relying on yours) below for information, maybe it is useful for others.

Thanks again,

Rik

== start code snippet
Sub Example2()
' From http://www.rondebruin.nl/copy3.htm
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long
Dim cnum As Long

'Fill in the path\folder where the files are
'MyPath = "\\ComputerName\YourFolder"
MyPath = "D:\Test\origs"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'clear all cells on the first sheet
basebook.Worksheets(1).Cells.Clear

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'start row
rnum = 2

'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
cnum = 1
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
Call My_Do_It("b9:b11", basebook, mybook, rnum, cnum)
Call My_Do_It("b23:b27", basebook, mybook, rnum, cnum)
Call My_Do_It("g36", basebook, mybook, rnum, cnum)

rnum = rnum + 1
mybook.Close savechanges:=False
Next Fnum
End If
CleanUp:
Application.ScreenUpdating = True
End Sub

Private Sub My_Do_It(ByVal Cellen As String, basebook As Workbook, mybook As
Workbook, rnum As Long, cnum As Long)
Set sourceRange = mybook.Worksheets(1).Range(Cellen)
SourceRcount = sourceRange.Rows.Count
With sourceRange
Set destrange = basebook.Worksheets(1).Cells(1, cnum). _

Resize(.Rows.Count, .Columns.Count)
End With
For x = 1 To SourceRcount
destrange.Cells(rnum, x).Value = sourceRange.Cells(x, 1).Value
Next x

cnum = cnum + SourceRcount
End Sub
== end code snippet
 
Back
Top