Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Rik
 
Posts: n/a
Default Combining data from cells from several excel sheets to a new sheet

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
CLR
 
Posts: n/a
Default Combining data from cells from several excel sheets to a new sheet

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" wrote:

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
Ron de Bruin
 
Posts: n/a
Default Combining data from cells from several excel sheets to a new sheet

Hi Rik

Try

http://www.rondebruin.nl/copy3.htm

Or formulas
http://www.rondebruin.nl/summary2.htm


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


"Rik" wrote in message ...
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



  #4   Report Post  
Posted to microsoft.public.excel.misc
Rik
 
Posts: n/a
Default Combining data from cells from several excel sheets to a new s

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" wrote:

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" wrote:

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
Rik
 
Posts: n/a
Default Combining data from cells from several excel sheets to a new s

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

"Ron de Bruin" wrote:

Hi Rik

Try

http://www.rondebruin.nl/copy3.htm

Or formulas
http://www.rondebruin.nl/summary2.htm


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


"Rik" wrote in message ...
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




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Importing Data Jillian Excel Worksheet Functions 9 December 23rd 05 12:45 PM
AUTO FILTER NOT CHANGING RESULTS Dejan Excel Discussion (Misc queries) 25 October 6th 05 02:01 PM
Using a relative SHEET reference for source data in a chart James Charts and Charting in Excel 6 August 16th 05 05:07 PM
Does excel recognise names rather than cells? Sue Excel Worksheet Functions 9 May 22nd 05 04:51 AM
Pulling data from 1 sheet to another Dave1155 Excel Worksheet Functions 1 January 12th 05 05:55 PM


All times are GMT +1. The time now is 09:06 AM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"