Collating data from two or more "Workbooks" into another "Workbook

[Prashanth KR]

Hi,

Currently, Iam doing a very tedious and time consuming job. Please help me
out on this.

I have several Workbooks say 30+ having only one worksheet with the same
layout i.e., Heading1, Heading2, Heading3 .... in the first sheet. The
Headings starts from B8:N8 and have some headings having a drop down list to
be picked up in each row beneath that till row no. 200.

Each workbook is named differently. My requirement is that I need to collate
these information by copying and pasting in another workbook named "Total
Data" of all these 30+ workbook information one below the other (i.e., from
Workbook1: Copy/Paste B9:N200 to the Workbook named "Total Data" and from
Workbook2: Copy/Paste B9:N200 to the Workbook named "Total Data" just below
the data copied from Workbook1....... and likewise for all Workbooks in the
"Total Data".

Secondly, I also want the name of each Workbook to be captured in Column A
in the master Workbook "Total Data" accordingly.

Please note that the Workbook "Total Data" has also got the same layout as
that
of the other Workbooks.

Can you please help me out in creating a Macro to do this task.

Your timely help will be greatly appreciated.
Prashanth KR.

 

[Tom Ogilvy]

Assuming all the workbooks and only the workbooks are in a single folder

sub ConsolidateData()
Dim bk as workbook, sh as worksheet
Dim bk1 as workbook, sh1 as worksheet
Dim rng as Range, r1 as Range
Dim sPath as String, sName as String
set bk = Workbooks("Total Data.xls")
set sh = bk.Worksheets(1)
sPath = "C:\Myfolder\"
sName = dir(spath & "*.xls")
do while sName <> ""
if lcase(sName) <> "total data.xls" then
set bk1 = workbooks.open(sPath & sName)
With bk1.Worksheets(1)
set rng = .Range(.cells(9,2),.Cells(rows.count,2).End(xlup))
set r1 = sh.cells(rows.count,2).End(xlup)(2)
if r1.row < 9 then set r1 = sh.Range("B9")
rng.Resize(,13).copy r1
sh.cells(r1.row,1).Resize(rng.count,1).Value =
Left(sName,len(sName)-4)
End with
end if
Loop
End Sub


It assumes total data.xls is already open when the macro is run. Change the
path to the directory that actually has the files to be consolidated.

Try the above untested code. It should get you started.

 

[Prashanth KR]

Hi Tom,

Nice to hear from you so quickly. But, I have a problem here. I changed the
sPath = "C:\Myfolder\" to the directory that actually has the files to be
consolidated.

Then I also changed the data to be collated by changing if r1.row < 9 then
set r1 = sh.Range("B9") to if r1.row < 9 then set r1 = sh.Range("B9:N200").

What is actually happening is that Iam getting the data of the first two
rows from the workbook1 only and not all the data from B9:N200. Secondly, it
just continues to add the same data of the first two rows one after the other
unless I abort the macro. It does not seems to collect the data from the
other sheets. ....

But Yes, Iam getting the workbook name in the column A, which is a good sign.

Kindly help.
Prashanth KR.