Pulling data from multiple workbooks

taxstar · Jun 16, 2008

I have about 60 separate Excel workbooks in similar formats, each of which has one worksheet with a unique list. I need to create a master list by accumulating all of those separate lists on a single worksheet in a separate workbook.

The workbooks will need to be linked so that the new master list is automatically updated when one of the separate lists is updated -- and be flexible enough to add data from new lists as they are created.

For what it's worth, all of the workbooks are in the same directory, the worksheet containing the unique list always has the same name, always uses columns A through R, but can be from 15 to 250 rows.

I know I can copy/paste special/link once for each of these worksheets - but I hope there's an easier way.

Any thoughts would be most appreciated.

BrianB · Jun 23, 2008

My code here should get you started

http://www.mrexcel.com/forum/showthread.php?t=140187

ravishankar · Jun 24, 2008

HI
try the following macro codes.

Code:

Sub extract()
Dim a As Integer, b As Integer, c As Integer, x As Integer
Dim f As String
Cells(2, 1).Select
f = Dir("D:\my documents\java\" & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
x = Cells(Rows.Count, 1).End(xlUp).Row
For a = 2 To x
d = Cells(Rows.Count, 1).End(xlUp).Row + 2
For b = 1 To 16
For c = 2 To 250
Cells(1, 1) = "='D:\my documents\java\[" & Cells(a, 1) & "]sheet1'!" & Chr(b + 64) & c
If Cells(1, 1) = "" Or Cells(1, 1) = 0 Then
Exit For
Else
Cells(d + c, b + 2) = Cells(1, 1)
End If
Next c
Next b
Next a
End Sub

Replace sheet1 with the name of sheet containing unique list and file path from D:\my documents\java\ to your file path (at 2 places) and run the macro. If everything goes well it will list all 60 file names in col A and Name list from col A to R of each file. 250rows x 16 columns x 60 files = 240000 values.It is going to be a slow process.
Ravi

ravishankar · Jun 24, 2008

HI
you can also try the following codes. it opens each file, copies the range and pastes to the master file.(In the previous code, no need to open each file.)

Sub cycle()
Dim a As Integer, b As String, d as integer, x As Integer, y As Integer, z As Integer
Dim f As String
Cells(2, 1).Select
f = Dir("C:\Raw Data\" & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
x = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "there are " & x & " files"
For a = 2 To x
b = Cells(a, 1)
Cells(1, 2) = b
Workbooks.Open Filename:="C:\Raw Data\" & b
y = Worksheets("unique list").Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:R" & y).Copy
ActiveWorkbook.Close
z = Cells(Rows.Count, 1).End(xlUp).Row + 2
Range("A" & z).PasteSpecial
Next a
MsgBox "Listing is complete."
End Sub

Ravi

taxstar · Jun 24, 2008

Thanks to ravishankar and BrianB for the suggestions. I had already gone through the 60-or-so copy/paste special/paste link procedures (VERY time consuming, I don't recommend it). It seems to work for now but I expect will become difficult to maintain over time. I look forward to trying your suggestions.

Taxstar

Pulling data from multiple workbooks

taxstar

Board Regular

Excel Facts

BrianB

Well-known Member

ravishankar

Well-known Member

ravishankar

Well-known Member

taxstar

Board Regular

Similar threads

Forum statistics

Share this page

Pulling data from multiple workbooks

taxstar

Board Regular

Excel Facts

BrianB

Well-known Member

ravishankar

Well-known Member

ravishankar

Well-known Member

taxstar

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock