Compile data from several worksheets to one.

**jdbaba** · 10-01-2010, 05:15 PM

Dear All,

I have like 100's of worsheet with the data. But I want to compile all the data into one worksheet. All the worksheets are almost similar. I just want to compile the particular column data of all the worksheets.

SUppose I have a worksheet name 10.15 and I have the data on E,F, and G column and I have other worksheet 10.20 and have the same type of data on the same columns but the row number changes a bit. NOw, I want to continuously arrange the data from these worksheets in one worksheet. Suppose the worksheet 10.15 has 15 rows and the worksheet 10.20 has 20 rows, I want a new spreadsheet with 25 rows which contains the data of both the worksheets.

Hope you good people know my problem.

Please help soon. Thanks.

Best Regards,
Jdbaba

**MarvinP** · 10-01-2010, 05:49 PM

How do you get 25 rows when the first had 15 and the second had 20?

**jdbaba** · 10-01-2010, 05:53 PM

I meant 35 rows for the new one.

**MarvinP** · 10-01-2010, 06:30 PM

I kind of knew that, but I needed to know you weren't using some new math on us.

So you want to grab a column of data from a bunch of worksheets and stick them on the bottom of a single worksheet. I kind of have the idea. Are all these worksheets in different workbooks? As in different .xlsx files or are they all in the same workbook?

To do this problem I need a small sample file. I would turn on the macro recorder and do about 2 of the copy and paste columns by hand. Then I'd look at the Macro code that is recorded to see what Excel VBA thought I'd done. Then I'd find the step that copied "on the bottom" of the column and change this code from a fixed Range to a variable. Test the new code and send it back to you to figure out.

Is that what you want? Can you supply a sample file(s)?

**jdbaba** · 10-02-2010, 09:45 AM

Dear MarvinP,

I have attached the sample spreadhseet with three sheets. What I meant by 100's of worksheets is like the sheets of the same spreadsheets (i.e single *.xls).

Here in the file, there are 4 columns of data starting from the same point in each of the worksheets but they necessarily don't end on the same row. So what I want is on the new sheet to list all the data continuously. I hope you understand. Thanks in advance.

**JBeaucaire** · 10-02-2010, 10:19 AM

'WORKBOOKS TO 1 SHEET
I have a macro that may be pretty close to "ready to use" 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.

**JBeaucaire** · 10-02-2010, 10:27 AM

Here's an edited version of the macro that will work with the non-standard layout represented on your sheets. If the data on each sheet started at A1 instead of C2, these edits wouldn't be necessary.

Please Login or Register  to view this content.

Put this macro into an empty workbook and name one sheet Master.
Edit the fPath variable to point to the correct folder. Then it should work fine.