Collating Data into one Worksheet

**anwitha** · 12-22-2010, 10:24 PM

I have a folder containing a number of workbooks each of which
contains a single worksheet. I am trying to combine just one worksheet
from each of these workbooks into one workbook preserving the information and
formatting, without all of the workbooks having to be open.

Currently I am collating this manually. Is there any alternative way to get it collated automatically? Please help.

Note - Example: Workbook 1
has 5 rows of data in 15 columns, workbook 2 has 8 rows of data in 15 columns.

**JBeaucaire** · 12-23-2010, 03:14 AM

'WORKBOOKS TO 1 SHEET STACKED
Here's a macro for collecting data from all files in a specific folder.

Workbooks to 1 Sheet

The parts of the code that need to be edited are colored to draw your attention. But essentially this macro is designed to do this...collect the data from multiple workbooks with varying lengths of data and stack all the data into a single sheet.

**anwitha** · 12-23-2010, 11:05 PM

Sir, When I pasted this code I am getting an error msg " Run-time Error 9, subsicript out of range". I am not able to find why this error comes because I am beginner in VB. Could you please attach the sample sheet with this code if possible.?

Thanks..

**watersev** · 12-24-2010, 01:18 AM

anwitha, can you post sample workbook?

**anwitha** · 12-24-2010, 02:37 AM

Hi,

I need to collate the workbook Sample sheet 1 and Sample shee 2 in one master sheet automatically using macro.

Attached the sample sheet for your ref.

**JBeaucaire** · 12-24-2010, 03:01 PM

The macro has to be edited. The error you're getting means you didn't edit it for your computer. I colored the parts you need to correct.

1) You need to put in the correct fPath to the folder on your computer where the files are.

2) You need to edit the Master to the name of the sheet you're collecting this data into.

3) Also this macro goes into that workbook.

4) You really should not have blank rows at the top of data sheets. Consider fixing that in the future so they aren't there.