Macro for collating data from multiple sheets to one table

[Johndinho]

Hi,

Newly registered but a long-time big fan of the help given out on this forum and confident someone will be able to help with this.

I have seen several threads on here with VBA to collate information to one sheet but I can't seem to get one to work.

I have 1186 files (All on my C Drive in the same folder) containing information in 86 different cells; i want to create a table using the information in each of the cells.

Help!! I don't want to have to type out 86000 pieces of data!!

Thanks,

Johndinho

 

[hiker95]

Johndinho,

Welcome to the MrExcel forum.

Be right back.

 

[hiker95]

Johndinho,

What version of Excel are you using?

What is the full path to the 1,186 files?

What is the file extension for the 1,186 files?

What is the worksheet name, in the 1,186 files, that contains the 86 cells.

Are the 86 cells in the same row, or column?

Or, are the 86 cells in the same range?


Can we have a screenshot of the master/consolidated workbook/worksheet where you want to create a table using the information in each of the cells?

Can we have a screenshot of two of the 86 workbooks/worksheets that contain the 86 cells?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker


Even better:

You can upload your master/consolidated workbook with the consolidated worksheet (manually formatted by you) from two additional worksheets that reflect the data in two of the 1186 files, to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[Johndinho]

HI Hiker,

What version of Excel are you using?
Excel 2010

What is the full path to the 1,186 files?
Both are listed on my Desktop as
C:\Test\TTTest.xlsx (master table)
C:\TestResults\*.xls (individual reports)

What is the file extension for the 1,186 files?
.xls

What is the worksheet name, in the 1,186 files, that contains the 86 cells.
Results

Are the 86 cells in the same row, or column?
No, some are in C and others in D.

Or, are the 86 cells in the same range?
No, spread out over 200 rows

Can we have a screenshot of the master/consolidated workbook/worksheet where you want to create a table using the information in each of the cells?
Can we have a screenshot of two of the 86 workbooks/worksheets that contain the 86 cells?

https://www.box.com/s/y5ln7fctximh09ce9oa9

This has got the table headers in and the cell references of where to collect the data from. (Thanks for the tip about Box Net )

Thanks for this - I think this is everything you asked for; let me know if you want anything else..

 

[hiker95]

ADVERTISEMENT

Johndinho,

Thanks for the workbook. But, it only contains one worksheet, Sheet2, with the reference cells to pull in.

Please attach another workbook, containing Sheet2, and three additional worksheets.

Each of the three additional worksheets will contain the raw data from three of the workbooks in C:\TestResults\*.xls (individual reports).

 

[Johndinho]

Apologies, I understand what you meant now..

New link: https://www.box.com/s/9o7bsqu7q2yt9ixcpq5g

Thanks

 

[hiker95]

ADVERTISEMENT

Johndinho,

Thanks for the latest workbook.

Is this an accurate display of your master workbook, cells, rows, columns? And, is cell A6 the beginning of the data being copied/pulled in?

Excel 2007
	A	B	C	D	E	F	G
1	1	2	3	4	5	6	7
2	Clause No.					5.1
3						Preliminary Checks
4
5	Unit Serial number	Main board Serial Number	Main board release number	RF module Serial number	RF Module Release number	Visual inspect & assembly check	Power ON and LED tests
6	C11	c12	c13	C14	C15	d57	d59
Sheet2

What is this information for?

Excel 2007
	CJ	CK	CL
1	85
2
3
4	Rework/Comments
5
6
7	TRUA-0078-01 SN00781000.xls
8	TRUA-0078-01 SN00781001.xls
9	TRUA-0078-01 SN00781002.xls
10	TRUA-0078-01 SN00781003 Eng.xls
Sheet2

 

[Johndinho]

A7 will be the start. That's just a cell reference to know where the info is coming from.

The idea is that the master sheet will be merged with another sheet as a searchable record.

 

[hiker95]

Johndinho,

For each row of information in Sheet2, beginning in cell A7, do you need to record in the same row (what column???) the workbook name that the information came from?

 

[Johndinho]

No thanks, he first column - the serial number - will act as the reference..